Desktop productivity for business analysts and programmers

concatenate in column names

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 7
Accepted Solution

concatenate in column names

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


Accepted Solutions
Solution
‎01-25-2018 06:30 AM
Valued Guide
Posts: 626

Re: concatenate in column names

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


All Replies
Valued Guide
Posts: 626

Re: concatenate in column names

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?

Occasional Contributor
Posts: 7

Re: concatenate in column names

Posted in reply to andreas_lds

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

Solution
‎01-25-2018 06:30 AM
Valued Guide
Posts: 626

Re: concatenate in column names

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;
Super User
Posts: 10,530

Re: concatenate in column names

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Occasional Contributor
Posts: 7

Re: concatenate in column names

Posted in reply to KurtBremser

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

Super User
Super User
Posts: 9,812

Re: concatenate in column names

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.

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 484 views
  • 0 likes
  • 4 in conversation