topic Concatenate the name of table with a column in SAS Programming

Concatenate the name of table with a column

SASdevAnneMarie — Tue, 17 Jan 2023 17:05:52 GMT

Hello Experts,

I'm wondering if it's possible in SAS to concatenate the name of table with the column.

I have a table ZC1, I have the column Date in this table, I would like to have the column Date_ZC1.

Thank you for your help !

Re: Concatenate the name of table with a column

svh — Tue, 17 Jan 2023 17:46:19 GMT

In case you need to re-name more than one variable in the table, this is a solution that works with all columns (variables) in your table. However, if you only need to rename one variable, the DATA step solution works well enough.

data ZC1;
input Date date9. x ;
cards;
01JAN2023 3
;
run;
   %macro rename;
proc sql;
   select distinct name into :var1- 
   from dictionary.columns
   where libname='WORK' and memname='ZC1' ;
   %let NUM_VARS = &sqlobs;
   %let DSN = ZC1;
   quit;
proc datasets library=WORK;
   modify &DSN;
   rename
   %do i = 1 %to &sqlobs;
   &&var&i = %sysfunc(catx(_,&DSN, &&var&i))
   %end;
   ;
   quit;
   run;
%mend;
%rename;

data ZC1 (rename=(Date=ZC1_Date)); /*This is a straightforward renaming of just the DATE variable*/
   set ZC1;
   run;

Re: Concatenate the name of table with a column

ballardw — Tue, 17 Jan 2023 18:08:27 GMT

Caution: Variable names are limited to 32 characters. SAS data set names are limited to 32 characters. You potentially are asking to create variable names that exceed the 32 character limit for a single variable.

The question might be why this is desirable or needed. Are you intending to merge multiple data sets and hence have multiple Date_ZC1 Date_ZC2 Date_ZC3 variables? If so, again there may be a reason to ask why? Such data is cumbersome to work with and often is not needed. If you add a variable that indicates the name of the data set source to such a set that results from appending data then you can use that variable for reporting or typically grouping responses/values for analysis.

Re: Concatenate the name of table with a column

SASdevAnneMarie — Tue, 17 Jan 2023 18:24:35 GMT

Hello,
Thank you for your message. I need it for the merging the rates for zc1, zc2, …
In real life I need to create rate_zc1, rate_zc2 because in the table zc1–zc20 I have only two columns Date and Rate. The column date_zc1 was for exemple. Rate+zcxx are less than 32 characters.

Re: Concatenate the name of table with a column

SASdevAnneMarie — Tue, 17 Jan 2023 18:25:17 GMT

Thank you, I need to rename only one column.

Re: Concatenate the name of table with a column

Tom — Tue, 17 Jan 2023 18:32:32 GMT

So you have two columns?

Let's assume they are and ID variable and the DATE variable you mentioned.

So first combine the data and keep track of where the data came from.

data tall;
  set zc1-zc20 indsname=indsname;
  by id;
  length dsname $32 ;
  dsname=scan(indsname,-1,'.');
run;

Now if you want you can use PROC TRANSPOSE to make a WIDE dataset instead.

proc transpose data=tall out=wide(drop=_name_) prefix=date_;
  by id;
  id dsname;
  var date;
run;

Re: Concatenate the name of table with a column

Kurt_Bremser — Tue, 17 Jan 2023 18:33:18 GMT

Do not merge the datasets, stack them (SET with multiple datasets and INDSNAME= option), and keep the dataset name as new column.

Re: Concatenate the name of table with a column

ballardw — Tue, 17 Jan 2023 22:37:59 GMT

@SASdevAnneMarie wrote:
Hello,
Thank you for your message. I need it for the merging the rates for zc1, zc2, …
In real life I need to create rate_zc1, rate_zc2 because in the table zc1–zc20 I have only two columns Date and Rate. The column date_zc1 was for exemple. Rate+zcxx are less than 32 characters.

Show how you intend to merge the rates and define what sort of rate is involved.

I have a strong suspicion that if you only have two variables as described, date and rate, that any combination of the existing rates to make some sort of composite rate may be problematic depending on the definitions of the actual "rate" involved. If the rate is something like "miles per gallon" any thing that combines different "rate" values that do not include the individual miles and gallons used to calculate the combined value is likely incorrect.

Re: Concatenate the name of table with a column

SASdevAnneMarie — Wed, 18 Jan 2023 14:23:56 GMT

Thank you Tom, that works !