Home

Ronein

Hello Why data set want2 is empty? I want to transpose char vars proc sql noprint; select name into :Char_varlist separated by ' ' from dictionary.columns where libname='SASHELP' and memname='CARS' and type='char' ; quit; %put Char_varlist=&Char_varlist; proc sql noprint; select name into :num_varlist separated by ' ' from dictionary.columns where libname='SASHELP' and memname='CARS' and type='num' ; quit; %put num_varlist=&num_varlist; data cars; set SASHELP.CARS; seq=_n_; Run; proc transpose data=cars (KEEP=seq &num_varlist.) out=want1(drop=_label_ rename=(_name_=Var_name COL1=value)); by seq; run; proc transpose data=cars (KEEP=seq &Char_varlist.) out=want2(drop=_label_ rename=(_name_=Var_name COL1=value)); by seq; run;

Ronein

Hello Lets say that I want to run statistics on multiple variables. Lets say that this code is working 100% and I get desired results. My question- Is there better way to write the code (using proc step instead of proc sql)? Please note that in real word I run it on 300 fields and data set has 2 million rows %macro dist(VAR,tbl,level,format); %do j=1 %to 6; %let version=%scan(&Vector_Versions.,&j.,+); proc sql; create table _dist_ as select Left("&VAR.") as Var_name LENGTH=300, put(&VAR.,&Format.) as Cat LENGTH=100, count(*) as nr, calculated nr/(select count(*) as total_nr from &tbl.) as PCT format=percent8.5 from &tbl. group by calculated CAT union all select Left("&VAR.") as Var_name LENGTH=300, 'Total' as CAT, count(*) as nr, 1 as PCT format=percent8.5 from &tbl. ; quit; proc sort data=_dist_; by var_name level cat_order; Run; proc append data=_dist_ base=r_r.Accum_tbl force;quit; %end; %mend dist; %dist(Var=Wealth,tbl=Y_ttt&version.,level=Y,Format=Fmt_1F.) %dist(Var=LoansBalance ,tbl=Y_ttt&version.,level=Y,Format=Fmt_continuous.) %dist(Var=AVG_PAYOFF_LAST_MON,tbl=Y_ttt&version.,level=Y,Format=Fmt_1F.) %dist(Var=HIYUV_EXLISCAR_AMT,tbl=L_ttt&version.,level=L,Format=Fmt_1F.) %macro dist(VAR,tbl,level,format); %do j=1 %to 6; %let version=%scan(&Vector_Versions.,&j.,+); proc sql; create table _dist_ as select Left("&VAR.") as Var_name LENGTH=300, put(&VAR.,&Format.) as Cat LENGTH=100, count(*) as nr, calculated nr/(select count(*) as total_nr from &tbl.) as PCT format=percent8.5 from &tbl. group by calculated CAT union all select Left("&VAR.") as Var_name LENGTH=300, 'Total' as CAT, count(*) as nr, 1 as PCT format=percent8.5 from &tbl. ; quit; proc sort data=_dist_; by var_name level cat_order; Run; proc append data=_dist_ base=r_r.Accum_tbl force;quit; %end; %mend dist; %dist(Var=Wealth,tbl=Y_ttt&version.,level=Y,Format=Fmt_1F.) %dist(Var=LoansBalance ,tbl=Y_ttt&version.,level=Y,Format=Fmt_continuous.) %dist(Var=AVG_PAYOFF_LAST_MON,tbl=Y_ttt&version.,level=Y,Format=Fmt_1F.) %dist(Var=HIYUV_EXLISCAR_AMT,tbl=L_ttt&version.,level=L,Format=Fmt_1F.)

shivrajpawar98

Hi All, I ran the content assessment and in the codeCheck i saw there are some SAS 9.4 procedures/Functions are giving issues or they are unsupported in SAS viya 4.so below is the list of procedures/function, i would really appreciate if anyone can help me to give thier viya 4 alternatives. DBLOAD CHART PLOT METADATA_GETNASN METADATA_GETNOBJ METADATA PRESENV DISPLAY METADATA_RESOLVE METALIB METAOPERATE DDEOption FSEDIT FORMATVALUE HDMD SQL METADATA_PURGE IOMOPERATE MDDB BUILD METADATA_PATHOBJ OLAP SAS_EXECFILENAME SOURCE Regards, Shivraj Pawar

jonburk

Hello, I'm unable to create a LIBNAME statement equivalent to a PROC SQL "CONNECT TO" statement that works perfectly. (I need a LIBNAME in order to use PROC APPEND.) Both statements use the same OLE DB driver, database and user. We are still at SAS version 9.4 M6, if that makes a difference. Here's my PROC SQL "CONNECT TO" statement: CONNECT TO oledb (init_string="Provider=MSOLEDBSQL19; Server=MyServer; Database=MyDatabase; UID=MyUsername; PWD=MyPassword; Use Encryption for Data=Optional; Trust Server Certificate=True;"); Here's my best guess at the equivalent LIBNAME statement: LIBNAME MyLibrary OLEDB PROMPT=NO DATASOURCE=MyServer PROVIDER=MSOLEDBSQL19 SCHEMA=dbo USER=MyUsername PASSWORD=MyPassword PROPERTIES=('Use Encryption for Data'=Optional 'Trust Server Certificate'=True); Unfortunately, it results in an "Invalid option name 'Use Encryption for Data'" error. NO MATTER WHAT I TRY, I SIMPLY CANNOT GET IT TO RECOGNIZE THE "Use Encryption for Data" and "Trust Server Certificate" OLE DB OPTIONS. But if I don't specify those options, I get the following error due to a lack of a certificate: Error trying to establish connection: Unable to Initialize: Client unable to establish connection. For solutions related to encryption errors, see https://go.microsoft.com/fwlink/?linkid=2227882.: SSL Provider: The certificate chain was issued by an authority that is not trusted. Prior to the upgrade of our database from SQL Server 2016 to SQL Server 2022 (and the upgrade from OLE DB driver SQLNCLI11 to MSOLEDBSQL19), the following LIBNAME statement worked: LIBNAME MyLibrary OLEDB PROMPT=NO DATASOURCE=MyServer PROVIDER=SQLNCLI11 SCHEMA=dbo USER=MyUsername PASSWORD=MyPassword; Any suggestions? Thanks!

SASuserlot

I am using the following code to generate the box plot. However My carmodel with Red color have only one record. I want to display the patterns to every Blue and Green colors, but not for the 'Red' color group . Is there any way I can control it? thank you in advance. /* Create dataset with 20 cars - Volvo/Fiat/Land Rover forced to Red */ data car_performance; length CarModel $20 Color $10; /* Define car models */ array car_models[20] $20 _temporary_ ( "Toyota Camry", "Honda Accord", "Ford Mustang", "Chevrolet Corvette", "BMW M3", "Tesla Model S", "Audi A4", "Subaru Outback", "Jeep Wrangler", "Porsche 911", "Lexus RX", "Hyundai Sonata", "Volkswagen Golf", "Mazda CX-5", "Mercedes C-Class", "Nissan Altima", "Kia Telluride", "Volvo XC90", "Land Rover Defender", "Fiat 500" ); /* Define colors (Green replaces Silver) */ array colors[2] $10 _temporary_ ("Blue", "Green"); /* Generate data */ do i = 1 to 20; CarModel = car_models[i]; /* Force Volvo, Fiat, Land Rover to Red */ if CarModel in ("Volvo XC90", "Fiat 500", "Land Rover Defender") then Color = "Red"; /* Distribute others evenly between Blue/Green */ else Color = colors[mod(i, 2) + 1]; /* Random MPG values (color-specific ranges) */ if Color = "Red" then MPG = round(18 + 12 * ranuni(0)); /* Red: 18-30 MPG */ else if Color = "Blue" then MPG = round(22 + 10 * ranuni(0)); /* Blue: 22-32 MPG */ else MPG = round(25 + 8 * ranuni(0)); /* Green: 25-33 MPG */ output; end; drop i; run; data xx; set car_performance; if CARMODEL in ('Volvo XC90' 'Land Rover Defender') and color = 'Red' then delete; run; options orientation = landscape errors = 2 missing = ' ' nofmterr ls = 175 validvarname = upcase nobyline; ods graphics on / attrpriority=none reset=all width=9.0in height=4.6in border=off; ods escapechar = '^'; ods results on; ods listing close; ods rtf file = "C:\temp\test.rtf" ; proc sgplot data=xx; title "Car MPG Distribution by Color"; vbox MPG / category=Color dataskin=none fillpattern lineattrs=(thickness=2); /* Customize colors in the plot */ styleattrs datafillpatterns=(X1); xaxis label="Car Color"; yaxis label="Miles Per Gallon (MPG)" grid; run; ods rtf close;