topic Naming transposed variables in SAS Programming

Naming transposed variables

jhh197 — Mon, 07 Aug 2023 22:21:27 GMT

I have a dataset which looks like below .

Date	PRICE_DS	Loc_typ
201301	65	25 MN SKP
201303	65	25 MN SKP
201310	65	25 MN SKP
201312	65	25 MN SKP
201401	65	25 MN SKP
201402	65	25 MN SKP
201301	79.25	SP NC KPP
201303	79.25	SP NC KPP
201310	79.25	SP NC KPP
201312	79.25	SP NC KPP
201401	79.25	SP NC KPP
201402	79.25	SP NC KPP

But when i transpose data i want output names which should look like below

proc transpose data= out=(drop=_name_ _label_) prefix=column;

by loc_typ;

var price_ds;

run;

But i want all variables to be named like below is that possible ? Can anyone please help ?

Column1	Column2	Column3	Column4	Column5	Column6	Column7
25 MN SKP	65	65	65	65	65	65
SP NC KPP	79.25	79.25	79.25	79.25	79.25	79.25

Re: Naming transposed variables

sbxkoenk — Mon, 07 Aug 2023 22:57:09 GMT

data have;
infile cards delimiter='|';
input Date $ PRICE_DS Loc_typ $;
cards;
201301 |  65      | 25 MN SKP
201303 |  65      | 25 MN SKP
201310 |  65      | 25 MN SKP
201312 |  65      | 25 MN SKP
201401 |  65      | 25 MN SKP
201402 |  65      | 25 MN SKP
201301 |  79.25   | SP NC KPP
201303 |  79.25   | SP NC KPP
201310 |  79.25   | SP NC KPP
201312 |  79.25   | SP NC KPP
201401 |  79.25   | SP NC KPP
201402 |  79.25   | SP NC KPP
;
run;

proc transpose data=have out=want(drop=_name_ _label_) prefix=column;
 by loc_typ;
 var price_ds;
run;

proc datasets library=work NoList NoDetails memtype=DATA;
 modify want;
  rename column6 = column7;
  rename column5 = column6;        
  rename column4 = column5;
  rename column3 = column4;
  rename column2 = column3;
  rename column1 = column2;
  rename Loc_typ = column1;
run;
QUIT;
/* end of program */

Koen

Re: Naming transposed variables

jhh197 — Mon, 07 Aug 2023 22:59:52 GMT

Thank you so much for the reply . Is there any other way if i have 40 columns i need to rename them manually right what if new date in database then i need to update code manually . Is there any automatic way ?

Re: Naming transposed variables

ballardw — Mon, 07 Aug 2023 23:11:09 GMT

Read you log or post your log. Your Proc Transpose code throws an error:

202  proc transpose data=have  out=(drop=_name_ _label_) prefix=column;
                                   -
                                   22
                                   76
ERROR 22-322: Expecting a name.
ERROR 76-322: Syntax error, statement will be ignored.
203  by loc_typ;
204  var price_ds;
205  run;

OUT= requires a data set name as the first thing.

You aren't doing anything with variable labels (at least as shown) so _label_ in the drop isn't needed.

Please provide data in the form of working data step code so we don't have to deal with artifacts from odd pictures or "table" layouts.

You want to rename your Loc_typ variable to column1 before use. Adding a variable for an ID statement will work with the prefix to generate the desired column labels.

data have;
 infile datalines dlm=',';
 input Date	PRICE_DS	Loc_typ $10.;
datalines;
201301,65,25 MN SKP
201303,65,25 MN SKP
201310,65,25 MN SKP
201312,65,25 MN SKP
201401,65,25 MN SKP
201402,65,25 MN SKP
201301,79.25,SP NC KPP
201303,79.25,SP NC KPP
201310,79.25,SP NC KPP
201312,79.25,SP NC KPP
201401,79.25,SP NC KPP
201402,79.25,SP NC KPP
;

data need;
   set have;
   by loc_typ;
   if first.loc_typ then row=2;
   else row+1;
   rename loc_typ=column1;
run;
proc transpose data=need
     out=want (drop=_name_ )
         prefix=column;
by column1;
id row;
var price_ds;
run;

If you just rename the By variable to column1 then you get a conflict with the output data and lose the first transposed column because you "stole" the column name.

Re: Naming transposed variables

sbxkoenk — Mon, 07 Aug 2023 23:17:04 GMT

data have;
infile cards delimiter='|';
input Date $ PRICE_DS Loc_typ $;
cards;
201301 |  65      | 25 MN SKP
201303 |  65      | 25 MN SKP
201310 |  65      | 25 MN SKP
201312 |  65      | 25 MN SKP
201401 |  65      | 25 MN SKP
201402 |  65      | 25 MN SKP
201301 |  79.25   | SP NC KPP
201303 |  79.25   | SP NC KPP
201310 |  79.25   | SP NC KPP
201312 |  79.25   | SP NC KPP
201401 |  79.25   | SP NC KPP
201402 |  79.25   | SP NC KPP
;
run;

proc transpose data=have out=want(drop=_name_ _label_) prefix=column;
 by loc_typ;
 var price_ds;
run;

proc contents data=want out=outputds;   run;
proc sort     data=outputds; by varnum; run;

data _null_; 
 set outputds end=last;
 if last then do; call symputx("maximum",varnum); end;
run;
%PUT &=maximum;

%MACRO ABCXYZ;
proc datasets library=work NoList NoDetails memtype=DATA;
 modify want;
%DO i=&maximum. %TO 2 %BY -1;
  rename column%eval(&i.-1) = column&i.;
%END;
  rename Loc_typ = column1;
run;
QUIT;
%MEND  ABCXYZ;

options mprint;
%ABCXYZ
QUIT;
/* end of program */

Koen

Re: Naming transposed variables

jhh197 — Tue, 08 Aug 2023 00:13:44 GMT

thank you so much for all the help