SAS code review for macro variables and modification to export data sets to local drive

[Snum]

Hello Everyone,

I want to create a programme that will run through all the datasets in a library and then use the proc freq to see the missing values in each datasets. Then export it to my local drive.

I have a code and would like to modify it a bit. I want to change the red highlighted text with the green highlighted ones. so that I can run the proc freq. Now the problem is I do not know how the create a macro for each datasets (like gphe.MHPRESP1001, it should run for all the 73 datasets in the library)

%let var1=gphl;
%let var2=gphe;


%macro xyz;

%do i=1 %to 2;

libname &&var&i. "/abc_&&var&i./prelock/data/raw/shared" access=readonly;

data &&var&i.._test;
set &&var&i...MHPRESP1001;
where MHDXDAT   MHOCCUR   MHSEV   MHSPID   MHTERM   MHSTDAT   MHONGO   MHENDAT   MHCAT_4   MHPRESP   MHLLTCDPRESP   DICT   DICTVER eq '';
run;
%end;

%mend;
%xyz;
ods output nlevels= MHPRESP1001;
proc freq data= gphe.MHPRESP1001 nlevels;
tables _all_ ;
run;
ods select all;

Then my plan is to export all the datasets from WORK lib to my local folder by using below

libname outfile xlsx "&path.\Forecast.xlsx";

proc copy in=work out=outfile;
run;

libname outfile;

Please can anyone help ?

[PaigeMiller]

The first step in the process of creating a macro is to create WORKING code for two cases of your situation, hard-coded (in this case, two data set names) that does what you want without macros and without macro variables. Emphasis on WORKING. Right now your code doesn't work, and will not run.

So please provide us with WORKING code for your situation, without macros and without macro variables, for two data sets hard coded.

Specifically, this line of code doesn't work, and there may be other errors.

where MHDXDAT   MHOCCUR   MHSEV   MHSPID   MHTERM   MHSTDAT   MHONGO   MHENDAT   MHCAT_4   MHPRESP   MHLLTCDPRESP   DICT   DICTVER eq '';

[Kurt_Bremser]

In your first LIBNAME, you use a typical UNIX path, but in your second LIBNAME, you have a backslash, pointing to a Windows system. If one of those works, the other can't work, depending on the operating system where SAS executes.

• What kind of SAS installation do you use? Single machine (desktop), or client/server?
• If the latter, which operating system on the server?
• Which interface do you use? Enterprise Guide, SAS Studio, or Display Manager (if single machine)?

[Snum]

I got it after much struggle and doing research (as I am not a programmer and a new bird in SAS).

proc sql; ** to get the count of datasets in a Lib**;
create table  My as
select distinct memname, libname from dictionary.tables where Libname="&lib" and memtype='DATA';

select distinct count(memname) into :cnt from my;
select distinct memname into :dataset1 - :dataset%left(&cnt) from my;
quit; 
**** macro to run thorough all the datasets in a Lib ***;
%macro Abc;
%do i= 1 %to &cnt;
ods output nlevels= &&dataset&i.;
proc freq data= &lib..&&dataset&i. nlevels;
tables _all_ ;
run;
ods select all;
data out.&&dataset&i.; 
set &&dataset&i.;
where NNonMissLevels=0;
run;
%end;
%mend;
%Abc;

[Tom · Accepted solution]

You are working way too hard to get the list of datasets.

proc sql noprint;
select distinct memname into :dataset1 -
  from dictionary.tables
  where libname=%upcase("&lib") and memtype='DATA'
;
%let cnt=&sqlobs;
quit; 

That type of code generation is much easier without macro code.  Just use a data step to generate the code directly from the source data without first transferring the data into macro variables.

It also might be easier to just build on single dataset that has the NLEVELS data for ALL of the datasets.

So first get the list of members and use that to generate the code to summarize each one and append the results to the summary dataset.  Since you will want to include information about the dataset that the variable live in you could use MODIFY statement in a data step to append instead of using PROC APPEND. Then it is easy to add the dataset name.

So here is code to get the dataset list and use it to generate the code to call PROC FREQ and append the results.

proc contents data=&lib.._all_ noprint out=contents;
run;
filename code temp;
data _null_;
  set contents;
  by memname;
  if first.memname;
  file code;
  put 'ods output nlevels=nlevels;';
  put 'proc freq data=' libname +(-1) '.' memname 'nlevels;tables _all_/noprint;run;' ;
  put 'data summary;'
    / 'if _n_=0 then modify summary;'
    / 'set nlevels;'
    / libname = :$quote. ';'
    / memname = :$quote. ';'
    / 'output;'
    / 'run;'
  ;
run;

Now setup an empty SUMMARY dataset and then run the generated code.

data summary;
  length libname $8 memname $32 TableVar $32 NLevels NMissLevels NNonMissLevels 8
         TableVarLabel $256
  ;
  stop;
  call missing(of _all_);
run;

ods select none;
%include code / source2;
ods select all;

Now you can pick from that SUMMARY dataset the observations you want.  Do you want those with no non-missing values?

data want;
  set summary ;
  where NNonMissLevels < 1 ;
run;

[Snum]

Thanks a lot TOM for looking into my code :). Your code worked . and Thanks a lot for the detailed explanation and wish you a happy new year .