BookmarkSubscribeRSS Feed
KrunalPatel
Calcite | Level 5

Hi All,

Anybody can please guide me how can I sequence the ID in proper order.

e.g.

I want to traspose below "Table1":

I want to transpose as:

Proc Transpose data= Table1 out=Table2;

by Code;

id Mth;

var Pay;

run;

while doing this i am not getting my ID in sequence

                                                            Table1:
CodeMthPay
ABC112
DEF212
XYZ134
ABC223
DEF145
XYZ265
ABC56
ABC412
ABC334
4 REPLIES 4
Keith
Obsidian | Level 7

The problem is that the order of the ID variables is determined by the order they appear in the input dataset.  You could solve the problem in your example data just by sorting table1 by Code and Mth, however this won't necessarily work in your real data.  There is no way I know of where you can specify the order of the variables within PROC TRANSPOSE, so one of your options is to reorder the output dataset afterwards.  Here's one way of doing that.

data table1;

input Code $ Mth Pay;

datalines;

ABC 1 12

DEF 2 12

XYZ 1 34

ABC 2 23

DEF 1 45

XYZ 2 65

ABC 5 6

ABC 4 12

ABC 3 34

;

run;

proc sort data=table1;

by code;

run;

Proc Transpose data=table1 out=table2 (drop=_:) prefix=Mth;

by Code;

id Mth;

var Pay;

run;

proc sql noprint;

select name into :vars separated by ','

from dictionary.columns

where libname='WORK' and upcase(memname)='TABLE2' and upcase(name) eqt 'MTH'

order by name;

create table table2 as select

code,

&vars.

from table2;

quit;

art297
Opal | Level 21

Not sure exactly what you want to end up with but, hopefully, the following will at least give you an idea.  p.s. the method shown below is from an old sas-l post by datanull (see: SAS-L archives -- October 2009, week 1 (#177)</title><style type="text/css"><!--BODY { font-family: ...

data table1;

  input Code $ Mth Pay;

  cards;

ABC 1 12

DEF 2 12

XYZ 3 34

ABC 2 23

DEF 1 45

XYZ 2 65

ABC 5 6

ABC 4 12

ABC 3 34

;

proc summary data=table1;

   output out=dateinfo(drop=_:) min(mth)=min max(mth)=max;

run;

data table2;

   if 0 then set table1;

   set dateinfo;

   do mth = min to max;

      output;

      end;

   do until(eof);

      set table1 end=eof;

      output;

      end;

   stop;

run;

proc sort data=table2;

  by code;

run;

proc transpose data=table2 out=want(where=(not missing(code))) prefix=mth;

   by code;

   var pay;

   id mth;

   idlabel mth;

run;

PGStats
Opal | Level 21

Same idea, using SQL :

data table1;
     input Code $ Mth Pay;
     cards;
ABC 1 12
DEF 2 12
XYZ 3 34
ABC 2 23
DEF 1 45
XYZ 2 65
ABC 5 6
ABC 4 12
ABC 3 34
;


proc sql;
create table tableX as
select *
from (select unique a.code, b.mth from table1 as a, table1 as b)
     natural left join table1
order by code, mth;
quit;

proc transpose data=tableX out=want(drop=_NAME_) prefix=mth;
     by code;
     var pay;
     id mth;
     idlabel mth;
run;

PG

Message was edited by: PG - simplified code, a bit.

PG
KrunalPatel
Calcite | Level 5

Thanks everyone. let me try the optoins mention by all of you.

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!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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