BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
sanjay1
Obsidian | Level 7


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;

%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

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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?

View solution in original post

3 REPLIES 3
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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?

sanjay1
Obsidian | Level 7

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.

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 413 views
  • 0 likes
  • 2 in conversation