BookmarkSubscribeRSS Feed
Ravikumarpa4
Obsidian | Level 7
Just I wanted to extract latest dataset in a library called common. I tried to use proc sql but it's display all the datasets, I need single dataset which id created recently and later I need to write macro for the same.

Any idea how to do it?
5 REPLIES 5
Patrick
Opal | Level 21

How do you know which data set is the latest? Is there some naming convention like a date in the name or a sequence number?

 

If so then you don't need to write a macro but you query the dictionary.tables and populate a macro variable for later use (see sample code below).

 

proc sql; 
  select cats(libname,'.',memname) into :my_table_name
  from dictionary.tables
  where libname='COMMON' and memname ....<some condition(s) selecting the required table>
  ;
quit;


data want;
  set &my_table_name;
run;
LinusH
Tourmaline | Level 20

As mentioned by @Patrick, DICTIONARY.TABLES should contain the information you need. I suspect that you could use the CRDATE and MODATE columns. To get the latest, use the max() function with a HAVING statement. 

Data never sleeps
Ravikumarpa4
Obsidian | Level 7
Hi
I used create and desc order but it displays all the records created by
date but now I want only top line record in macro.

##- Please type your reply above this line. Simple formatting, no
attachments. -##
Reeza
Super User

How is this different than your previous question which had several worked examples? 

 

https://communities.sas.com/t5/SAS-Procedures/How-to-write-macro-to-extract-latest-months-dataset/m-...

 

If you absolutely want a macro because it's your homework wrap the code between %macro and %mend. 

 

%macro latest_date();

 

*code already provided;

 

%mend;

 

%latest_date;

 

If you want some sort of dynamic nature to this you need to be more explicit in your requirements. You could add parameters for library name and what you wanted the macro variable to be called. But you haven't explained what you want. 

 

Here's a link on into to macro programming to help you change the code if required. 

 

http://www.ats.ucla.edu/stat/sas/seminars/sas_macros_introduction/

Kurt_Bremser
Super User

@Ravikumarpa4 wrote:
Just I wanted to extract latest dataset in a library called common. I tried to use proc sql but it's display all the datasets, I need single dataset which id created recently and later I need to write macro for the same.

Any idea how to do it?

See this example:

16         proc sql noprint;
17         select name into :names from sashelp.class order by name descending;
18         quit;
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.01 seconds
      cpu time            0.01 seconds
      

19         %put "names=&names";
"names=William "

Note that the macro variable contains only one entry, coming from the first line that the order by clause delivers.

So

proc sql noprint;
select memname into :memname from dictionary.tables
where libname = 'COMMON'
order by memname descending;
quit;

should deliver the last dataset from library COMMON in macro variable &memname.

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
  • 5 replies
  • 3360 views
  • 0 likes
  • 5 in conversation