BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Ravikumarpa4
Obsidian | Level 7
There are multiple datasets with name data_yyyymm format but now I want to extract latest month data set by writing macro how can I do. Example if I'm running macro in September I'll be having August dataset which I need extract some time August month dataset not available in that case macro need to extract July month dataset.


1 ACCEPTED SOLUTION

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

Why do you have data in the dataset name?  Now this isn't necessarily you, it seems that quite a few people insist on putting date information in dataset names, but it is the direct cause of making you have this type of problem.  Use one large dataset with a variable for date, this then trivialises the problem as code can be written for the one structure/name, and subsetting is simply where clausing.

 

As for coding:

data xyz_201406;
  a=1;
run;
data xyz_201405;
  a=2;
run;
data tmp;
  set sashelp.vtable (where=(libname="WORK" and substr(memname,1,3)="XYZ"));
  dt=scan(memname,2,"_");
run;
proc sort data=tmp;
  by dt;
run;

This shows how you can use the sashelp tables to identify certain datasets, however it is still patching the actual problem of file setup.

View solution in original post

6 REPLIES 6
LinusH
Tourmaline | Level 20

First yo need a way to get a specific (the prior month initially).

This is accomplished with the INTNX function, which you could call from a data step, or via %sysfunc in open code/macro.

 

The you need to verify that the specific month data set exists. 

Here you got a lot of options, like DICTIONARY.TABLES in SQ, SASHELP.VSTABLE from other SAS programs, data step SAS file  functions.

 

If the specific month doesn't exist, you need to adjust the month with INTNX again. Depending on the how dynamic you wish to this, us %IF %THEN %ELSE logic, or some kind of loop logic.

 

This would get you started, get back when you specific questions a long the way.

Data never sleeps
Ravikumarpa4
Obsidian | Level 7
Hi Team,

Can you give me the code.

Example in particular library I have many datasets but I wanted to know
latest dataset so I want to write macro for the same.

Regards
Ravikumar

##- Please type your reply above this line. Simple formatting, no
attachments. -##
SASKiwi
PROC Star

I did give you some code to try. Have you tried it?

Ksharp
Super User

No need Macro.


data data_201608;
set sashelp.class;
run;
data data_201609;
set sashelp.class;
run;




proc sql;
select memname into : want 
 from dictionary.members
  having libname='WORK' and memtype='DATA' 
   and input(scan(memname,-1,'_'),best.)=
       max(input(scan(memname,-1,'_'),best.));
quit;

%put &want ;

RW9
Diamond | Level 26 RW9
Diamond | Level 26

Why do you have data in the dataset name?  Now this isn't necessarily you, it seems that quite a few people insist on putting date information in dataset names, but it is the direct cause of making you have this type of problem.  Use one large dataset with a variable for date, this then trivialises the problem as code can be written for the one structure/name, and subsetting is simply where clausing.

 

As for coding:

data xyz_201406;
  a=1;
run;
data xyz_201405;
  a=2;
run;
data tmp;
  set sashelp.vtable (where=(libname="WORK" and substr(memname,1,3)="XYZ"));
  dt=scan(memname,2,"_");
run;
proc sort data=tmp;
  by dt;
run;

This shows how you can use the sashelp tables to identify certain datasets, however it is still patching the actual problem of file setup.

SASKiwi
PROC Star
%let Run_Date = %sysfunc(today());
%let Last_Month = %sysfunc(intnx(MONTH, &Run_Date, -1), yymmn6.);

data want;
  set have_&Last_Month;
run; 

 

By adding the two macro statements to the top of your program, the last month macro variable suffix can be used on any dataset that follows. 

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!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 6 replies
  • 2154 views
  • 1 like
  • 5 in conversation