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

Hi,

 

Currently I have columns in SAS table called "OUTCOME_MAR-" , "OUTCOME-APR-", "OUTCOME-MAY-" etc and in the same table I have a column called YEAR which simply contains the year in every row (user-defined by a prompt at start of the process).

OUTCOME_MAR-OUTCOME_APR-OUTCOME_MAY-OUTCOME_JUN-YEAR
10203052018
201520152018
101010202018
15105302018

 

 

 

Say for example if the YEAR column is populated with '2018' I then want to be able to automatically rename my columns to "OUTCOME_MAR-2018", "OUTCOME_APR-2018" in the query builder, is this possible? I can't see how I can use any formula in the 'column name' text box where I can reference other data or concatenate values. This is what I want:

OUTCOME_MAR-2018OUTCOME_APR-2018OUTCOME_MAY-2018OUTCOME_JUN-2018
1020305
20152015
10101020
1510530

 

Thanks

1 ACCEPTED SOLUTION

Accepted Solutions
andreas_lds
Jade | Level 19

Maybe something like this:

 

data work.narf;
   length outcome_Mar outcome_Apr outcome_May outcome_Jun Year 8;

   infile datalines;
   input outcome_Mar outcome_Apr outcome_May outcome_Jun Year;

   /* remove this and use the prompt-variable when building the label */
   if _N_ = 1 then do;
      call symputx('year', Year);
   end;

   datalines;
10 20 30 5 2018
20 15 20 15 2018
10 10 10 20 2018
15 10 5 30 2018
;
run;

data _null_;
   set sashelp.vcolumn(
         keep= Name LibName MemName 
         where= (LibName = 'WORK' and MemName = 'NARF' and lowcase(Name) like 'outcome%')
      ) end=jobDone;


   length Label $ 100;

   /* replace &year with prompt-var */
   Label = cats(Name, "-&year.");

   if _n_ = 1 then do;
      call execute('proc datasets library= work nolist nodetails;  modify Narf; label');
   end;

   call execute(catx(' = ', Name, quote(trim(Label))));

   if jobDone then do;
      call execute('; run;quit;');
   end;
run;


ods excel file="&Benutzer\temp\label.xlsx";

proc print data=work.narf label noobs;
   var outcome_:;
run;

ods excel close;

View solution in original post

6 REPLIES 6
andreas_lds
Jade | Level 19

It is highly recommended not to have month and year information in variable names. Store those information in variables and it is most likely that processing the data will be much easier.

 

Additionally: don't use special chars in variable names.

 

If you really have to create such crazy names: Are you 100% sure that there is always only one value in the variable year?

nnn123
Fluorite | Level 6

Thanks Andreas,

 

I want the column names to be like that purely because they are to be exported straight into an Excel file for publication, and the heading names in Excel are the format of "OUTCOME_MAR-2018" and can not be changed.

 

There will definitely only ever be one year in the YEAR field because the person running the job has chosen the year earlier via a prompt screen. If for example next year the user selects 2017, then I want the columns to automatically default to OUTCOME_MAR-2017

andreas_lds
Jade | Level 19

Maybe something like this:

 

data work.narf;
   length outcome_Mar outcome_Apr outcome_May outcome_Jun Year 8;

   infile datalines;
   input outcome_Mar outcome_Apr outcome_May outcome_Jun Year;

   /* remove this and use the prompt-variable when building the label */
   if _N_ = 1 then do;
      call symputx('year', Year);
   end;

   datalines;
10 20 30 5 2018
20 15 20 15 2018
10 10 10 20 2018
15 10 5 30 2018
;
run;

data _null_;
   set sashelp.vcolumn(
         keep= Name LibName MemName 
         where= (LibName = 'WORK' and MemName = 'NARF' and lowcase(Name) like 'outcome%')
      ) end=jobDone;


   length Label $ 100;

   /* replace &year with prompt-var */
   Label = cats(Name, "-&year.");

   if _n_ = 1 then do;
      call execute('proc datasets library= work nolist nodetails;  modify Narf; label');
   end;

   call execute(catx(' = ', Name, quote(trim(Label))));

   if jobDone then do;
      call execute('; run;quit;');
   end;
run;


ods excel file="&Benutzer\temp\label.xlsx";

proc print data=work.narf label noobs;
   var outcome_:;
run;

ods excel close;
Kurt_Bremser
Super User

Don't do that. Apart from the fact that dashes have no place in variable names, transpose the data so that the months, years and values are stored in separate variables in a long format. Then consolidate months and years into SAS dates, and you have intelligent data that lets you write intelligent programs.

nnn123
Fluorite | Level 6

Hi Kurt,

 

thanks for your input - unfortunately the column headings have to be like that in the Excel file this output will be pasted into, I realise they are not ideal. I just wondered if any formula (concatenate etc) can be worked into column names in any way

RW9
Diamond | Level 26 RW9
Diamond | Level 26

This "unfortunately the column headings have to be like that in the Exce" - is unfortunately common thinking now.  You do not have to work with your data in SAS like you do in Excel.  If at the reporting stageyou need to create an Excel file, then at that point you transpose the data and do all the other bad practices associated with Excel, you do not work this way throughout.  

Now that being said you can use arrays, however there are some restrictions and your code will be very messy.  Firs you need to provide actual test data in the form of a datastep - use this post if you are unsure:

https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat...

And post the datastep in a code window (its the {i} above post).  

As for doing this in Query Builder, no, you don't have any chance, you need programming - arrays and loops and such like to handle such nonsense - garbage in and all that.

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
  • 6 replies
  • 4146 views
  • 0 likes
  • 4 in conversation