topic Re: Do Loop Macro in SAS Programming

Do Loop Macro

sanjay1 — Tue, 07 Aug 2018 08:59:35 GMT

Hi Experts,

I have a library with many datasets of same variables. I am tryng to count the number of observations of each dataset by names and place them in a master dataset(result).

Below are the datsets which ends with daykey.

libname ab "/c:/users/desktop/files";

data_201800107
data_201800114
data_201800121
data_201800128
data_201800207
data_201800214
data_201800221
data_201800228
data_201800302
data_201800309
data_201800316
data_201800323
data_201800320

I can do this with the below code.

data result;
if _n_ = 1 then delete;
run;

%macro test;
%do i = 0 %to 87;
data _NULL_;
OFFSET1=&i;
X1=PUT(DAY(INTNX('Day',TODAY(),-(OFFSET1+0),'b')),Z2.);
y1=PUT(MONTH(INTNX('Day',TODAY(),-(OFFSET1+0),'b')),Z2.);
z1=PUT(YEAR(INTNX('Day',TODAY(),-(OFFSET1+0),'b')),$4.);
dayk=z1||y1||x1;
call symput("dayk",dayk);
run;
%put &dayk;

%if %sysfunc(exist(ab.data_&dayk.)) %then %do;

proc sql;
create table new_&dayk. as
select count(*) as count,name, from ab.data_&dayk.
group by name;
quit;

data new_&dayk.;
set new_&dayk.;
rundate = "&dayk.";
run;

data result; /*for master data creation */
set result new_&dayk.;
run;

%end;

%mend test;

%test;

But when these data sets have month and date how can I do this.

Below are the sample datasets.

libname ab "/c:/users/desktop/files";

data_jan_201800107
data_jan_201800114
data_jan_201800121
data_jan_201800128
data_feb_201800207
data_feb_201800214
data_feb_201800221
data_feb_201800228
data_mar_201800302
data_mar_201800309
data_mar_201800316
data_mar_201800323
data_mar_201800320

Thanks & Regards

Sanjay

Re: Do Loop Macro

RW9 — Tue, 07 Aug 2018 09:10:19 GMT

You do not need to do any of that:

data want;
  set sashelp.vtable (where=(libname="AB") keep=libname memname nobs);
run;

Two tips, if you start creating lots of macro to process your data then consider that you have modelled your data in a less than optimal way. Putting data in dataset names is not a good idea, its makes all your programming effort a lot more difficult and less robust. Also takes more space.

Oh, I just noticed you want by groups not overall, so change to:

data want;
  length dataset $200;
  set ab.data_: indsname=tmp;
  dataset=tmp;
run;

proc sql;
  create table want as
  select dataset,
         name,
         place,
         count(*) as tot
   from  want
   group by dataset,
            name,
            place;
quit;

You will note that I put all the data together - and you will see how much simpler this makes coding.

Also note that the syntax:

libname ab "/c:/users/desktop/files";

Is invalid, are you working on Windows, or UE version or something?

Re: Do Loop Macro

sanjay1 — Tue, 07 Aug 2018 11:32:42 GMT

I am working on windows.

How can I check one particualr dataset volume from want dataset just to make sure that its reading right.

Example : From want dataset if I want to read only data_jan_201800114 how can I approach this.

Re: Do Loop Macro

RW9 — Tue, 07 Aug 2018 11:52:00 GMT

In my code, you can filter the dataset with a where clause, or just look at the sumarised data or if its just one dataset you want to look at rather than using prefix:, just put the dataset name e.g:

data want;
  length dataset $200;
  set ab.data_jan_201800114 indsname=tmp;
  dataset=tmp;
run;

proc sql;
  create table want as
  select dataset,
         name,
         place,
         count(*) as tot
   from  want
   group by dataset,
            name,
            place;
quit;