BookmarkSubscribeRSS Feed
Obsidian | Level 7
I have many variables in a database by date: Name variables: XXXX_20220101 YYYY_20220101 ZZZZ_20220101 WWWW_20220101 XXXX_20220202 YYYY_20220202 ZZZZ_20220202 WWWW_20220202 XXXX_20220303 YYYY_20220303 ZZZZ_20220303 WWWW_20220303 ... I want to select all the variables of a date in a new database. How do I do it? where scan(all,-1,'_') = 20230612;
Super User Tom
Super User

Explain more about how the data is structured.  Why do you have dates stored in the NAME of the variable.  It is best to store data in the VALUE of a variable instead.  


If you have a variable (or set of variables) that uniquely identify the observations in your dataset perhaps you should first transpose the data.

proc transpose data=have out=tall;
  by id;

Then you can select the observations from the TALL dataset what came from variables that had a particular pattern to their names.

data want;
  set tall;
  where _name_ like '%^_20230612' escape '^' ;

If you really want to perpetuate the problem of having dates in variable names you could build a list of variable names into a macro variable

proc sql noprint;
select nliteral(name) into :varlist separated by ' '
 from dictionary.columns
 where libname='WORK' and memname='HAVE'
   and name like '%^_20230612' escape '^'

and use the macro variable to generate part of the code to select the variables of interest. 

data want;
  set have;
  keep id &varlist;


Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.


Register now!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

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
  • 1 reply
  • 1 like
  • 2 in conversation