BookmarkSubscribeRSS Feed
CMG3
Calcite | Level 5

Is there a straightforward way in EG to add a prefix (such as year) to a list of 100+ variables?  I've found some potential macros I could insert, but I'm wondering if there's another way to do this within EG 5.1.

Thanks in advance for any help you can provide!

14 REPLIES 14
Reeza
Super User

How did the 100 plus variables get created? Sometimes modifying a previous step is easier.

CMG3
Calcite | Level 5

That's how the government sends it...I just take what they give me!

Reeza
Super User

So the first step is an import procedure, is the file CSV, Excel or a SAS dataset?

In the import procedure you might be able to use a default input such as year1-year190. 

CMG3
Calcite | Level 5

I have 20 SAS data sets for each of 7 years.  One of the problems is that several of the data sets have the same variable names...for example there's an "ËVENT" variable in 5 of the data sets each year.  I'm trying to give the variables in the 20 data sets a prefix that identifies the year and data set.  Since I'm working with SAS datasets I haven't been importing anything, just opening them...maybe I'm missing something?

Reeza
Super User

A better way might be to add a variable to the dataset when you stack them that indicates the year of the file?

This will allow you to do by processing on the variables as needed.

CMG3
Calcite | Level 5

I may not be following your suggestion, but the other issue is that several of the data sets within the same year contain the same variable names.  Currently it looks like this:

2005--OP

               ID     Name      Event1     Event2    

Jones

Smith

2005--IP

               ID     Event1     Var5         Var7

Jones

Smith

2005--RX

               ID     DQ     Event1     Event2    

Jones

Smith

So what I'm trying to do is add a prefix to the variable "Event" so I'll know the year and data set, to get something like  " _05_OP_Event1", "_05_IP_Event1" and "_05_RX_Event1".  2006 would be "_06_OP_Event1", etc.  At least that's what I think I'm trying to do...:)

Reeza
Super User

You should add variables to your dataset instead. This way you can filter more easily in your queries later on, rather than try and sort through a ton of variables.

There are reasons why you may not want the type split out like this, if you're trying to create a patient level records for instance, but year should probably go like this. It really depends on what you plan to do with it downstream. The benefit of this method is you can proc transpose it later on and use some of the auto renaming options available.

For example

ID Name EVENT1 EVENT2 Type  Year
                                             IP  2005

                                             OP 2005

                                              RX 2005

                                          IP  2006

                                        

OP 2006

RX 2006
CMG3
Calcite | Level 5

In essence I am trying to create patient level records and associated survey responses.  I'll be doing cross-sectional and longitudinal analysis...so it sounds like you're suggesting I think about the data set up in terms of person-year rather than just person for cross-sectional analysis?  Get at the 2005 records/responses by selecting by year=2005, etc.  And when I get to the longitudinal analysis use the proc transpose to give me the wide file? 

CMG3
Calcite | Level 5

Thanks to everyone for the help and insight...

Haikuo
Onyx | Level 15

Well, if they are SAS tables, then try following code, it will put table names as prefix for all variables in the corresponding table.

/*get the names of all of your tables*/

proc sql NOPRINT;

  select DISTINCT cats( MEMNAME) into :DSN SEPARATED BY ' ' from dictionary.columns where LIBNAME='YOURLIBNAME';QUIT;

  %MACRO RE;

    %DO I=1 %TO %SYSFUNC(COUNTW(&DSN));

   %LET DS=%SCAN(&DSN,&I);

proc sql NOPRINT;

  select cats(name,'=',memname,'_',name) into :ren SEPARATED BY ' ' from dictionary.columns where LIBNAME='YOULIBNAME' AND MEMNAME="&DS";QUIT;

  PROC DATASETS LIB=WORK;

   MODIFY &DS;

   RENAME &REN;

   RUN;

%END;

%MEND;

%RE;

Haikuo

CMG3
Calcite | Level 5

Thanks for the help!  It looks like this is not a job for SAS EG...

Haikuo
Onyx | Level 15

You got me confused. All of the Base SAS code CAN be run in EG. As a matter of fact, I have built and tested those code in EG 5.1. So now I am really lost.

Haikuo

CMG3
Calcite | Level 5

You forgot the novice part of my question...:)  I'll see if I can figure out how to use the code within EG.

Haikuo
Onyx | Level 15

Ok, now I got it.

Fire up EG > File > New > Program, then copy & paste, change the Libname= to your real library name, has to be capital. Then try to run it.

Haikuo

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!

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
  • 14 replies
  • 1510 views
  • 6 likes
  • 3 in conversation