concatenate in column names

[nnn123]

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
10	20	30	5	2018
20	15	20	15	2018
10	10	10	20	2018
15	10	5	30	2018

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-2018	OUTCOME_APR-2018	OUTCOME_MAY-2018	OUTCOME_JUN-2018
10	20	30	5
20	15	20	15
10	10	10	20
15	10	5	30

Thanks

[andreas_lds]

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]

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 · Accepted solution]

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]

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]

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]

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.