Hi I've a simple SQL work to read in several data sets and keep selective variables from the data, so I used macro. Since the variable list is long and etc. I thought I'd spell out using %let before the macro. When calling in the macro %get, it apparently doesn't recognize these parameters. What do I do, make them system variables? Hope it makes sense. Thanks in advance.      %let yr=2017; %let yr2=2018; %let DB1var=v1 v2 v3 v4; %let DB2var=x5 x7 x14 x5; %let DB1name=mylib1.dt_A&fy; *mylib1.dt_A2017; %let DB2name=mylib2.dt_B&fy2; *mylib2.dt_B2018; %macro get (db=, var=, dbname=); proc sql; create table &db as select a.*, b.* from have (where= (&db=1) ) as a left join &&dbname (keep=&dbvav) as b on a.ID=b/OD; quit; %mend; %get (db=DB1, var=DB1var, dbname= DB1name) ) ; %get (db=DB2, var=DB2var, dbname= DB1name) ) ; /*It is how it should be executed if I'm doing it right*/ proc sql; create table DB1 as select b.* from have (where= (DB1=1) ) as a left join DT_A2017 (keep=v1 v2 v3 v4) as b on a.ID=b.ID; quit; /*What it actually turns out*/ proc sql; create table DB1 as select b.* from have (where= (DB1=1) ) as a left join DB1name (keep=DB1var) as b on a.ID=b.ID; quit;     ... View more

I've a large data set (Data A, easily > 500K rows) with a long list of conditions  (A01...A38, B02... B75, C04..... S99). I want to add variable description using an external data set (Data B), with a corresponding list of variable names and description.   I found a SAS code but it works on occasions, e.g. on A01-B20, but not when I tried to include all variables. I don't know why. Also I'm hoping the code can work efficiently as my data can be large.   The sample code is as below. The actual label data and the data to be applied (w/ 100 cases) are attached so that you can test the macro % varlabel on them, and it may help to see why the macro works on the sample data but not the actual data . Thanks. Or any other SAS codes that would work for my purpose would do too. Thanks in advance.   data labeldata; input var $ 1-3 labels $ 5-17; datalines; A01 Diebetes A02 Heart disease B01 Cancer B02 Renal failure ; data mydata; input ID 1 A01 3 A02 5 B01 7 B02 9; datalines; 1 1 1 1 0 2 0 0 0 1 3 1 0 1 1 4 1 0 0 1 ; run; %macro varlabel(indata=, lib=, labeldata=, labelvar=, labeldesc=); data _null_; set &labeldata; call symput("var" || trim(left(_N_)), trim(left(&labelvar))); call symput("label" || trim(left(_N_)), trim(left(&labeldesc))); call symput("nobs", trim(left(_N_))); run; proc datasets library = &lib memtype = data nolist; modify &indata; label %do i = 1 %to &nobs; &&var&i = &&label&i %end; ; quit; run; %mend; %varlabel(indata=mydata, lib=work, labeldata=labeldata, labelvar=var, labeldesc=labels); proc contents data=mydata order=varnum; run;         ... View more

I've a data set and would like to find the most frequent value from variables, along with max number of response and the count for the most frequent value.     data have; input id 1 fy2004 $ 3-4 fy2005 $ 6-7 fy2006 $ 9-10 fy2007 $ 12-13 mostfreq $ 17-18 count 20 total_count 22; datalines; 1 13 13 13 13 13 4 4 2 14 14 14 14 3 3 3 12 12 12 05 12 3 4 4 01 01 02 01 2 4 5 01 01 12 12 02 2 4 ; data want; set have; length MostFreq $2; array list fy:; array _t[10] _temporary_; call missing(of _t[*]); do i=1 to dim(list); if list[i] ne '' then _t[input(list[i],2.)]+1; end; Count=max(of _t[*]); MostFreq=put(whichn(Count, of _t[*]),z2.); drop i; run; The code came from https://communities.sas.com/t5/General-SAS-Programming/Find-most-frequent-response-across-multiple-variables/td-p/269774. I modified it but don't know why it didn't work on my data. I also need a third variable  for total count of responses.   Much appeciated for your help.   ... View more

I've different years of data, with file names in fiscal year format, e.g. 1819 for 2018 data, 1718 for 2017 data, etc. And I need to retrieve the data going back to previous 5 years. Here the code I tried to write but it didn't work.  - the last year of data is 2018, thus %let LastYrQ4 = 1819Q2 (Q2 as for the 2nd Quarter) - Use %i = 1 %to 5 to get data going from 2018 to 2014  So Yr&i is to generate values 2018, 2017, 2016, 2015 and 2014 and  UrT&i is to generate values 1819, 1718, 1617, 1516, 1415   Not sure why this code didn't work. Any tips? Thanks.   %let LastYrQr = 1819Q2; %let Yr = %sysfunc(SUBSTR(&LastYrQr,3,2)); %macro getFYs(); %do i = 1 %to 5; %let Yr&i = 20%sysfunc(putn(%eval(&Yr-&i),z2.)); %let YrT&i = %sysfunc(putn(%eval(&Yr-&i),z2.))%sysfunc(putn(%eval(&Yr+1-&i),z2.)); %end; %mend; %getFYs();   ... View more