want to convert data

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 78
Accepted Solution

want to convert data

I have data in this format.

data test;

input emp_code Jan Feb Mar Apr;

datalines;

a 1 1 2 3

b 2 3 2 2

c 1 5 6 3

d 2 6 3 5

e 1 1 1 1

f 3 6 5 2

;

run;

want to covert in thi form .

emp_codeMap_codeMth_key
a1Jan
b2Jan
c1Jan
d2Jan
e1Jan
f3Jan
a1Feb
b3Feb
c5Feb
d6Feb
e1Feb
f6Feb
a2Mar
b2Mar
c6Mar
d3Mar
e1Mar
f5Mar

Accepted Solutions
Solution
‎03-20-2014 06:01 AM
Trusted Advisor
Posts: 1,137

Re: want to convert data

Posted in reply to TarunKumar

Alternatively by proc transpose, i used format ord to sort the data as to get the desired output.

proc format;

    invalue ord 'Jan'=1

                'Feb'=2

                'Mar'=3

                'Apr'=4;

run;

proc sort data=test;

    by emp_code ;

run;

proc transpose data=test out=want(rename=(_name_=Map_code    col1=Mth_key));

    by emp_code ;

run;

data want_;

    set want;

    order=input(map_code,ord.);

run;

proc sort data=want_ out= want_y(drop=order);

    by order emp_code Mth_key map_code;

run;

Thanks,

Jag

Thanks,
Jag

View solution in original post


All Replies
Contributor
Posts: 45

Re: want to convert data

Posted in reply to TarunKumar

/* size of dataset*/

proc sql;

select count(*) into : numobs from have;

quit;

data want;

set /* set together */

have(keep= emp_code Jan rename=(Jan=Map_Key))

have(keep= emp_code Feb rename=(Feb=Map_Key))

have(keep= emp_code Mar rename=(Mar=Map_Key))

have(keep= emp_code Apr rename=(Apr=Map_Key))

;

if _n_/&numobs. le 1 then Mth_key = "Jan";

else if _n_/&numobs. le 2 then Mth_key = "Feb";

else if _n_/&numobs. le 3 then Mth_key = "Mar";

else Mth_key = "Apr";

run;

Super User
Super User
Posts: 7,977

Re: want to convert data

Posted in reply to Murray_Court

Hi,

Can I suggest another possibility.  The above is great for a fixed number of months, however its likely to be more than that.  Try the below which will loop for each month in the test dataset:

data test;
input emp_code $ Jan Feb Mar Apr;
datalines;
a 1 1 2 3
b 2 3 2 2
c 1 5 6 3
d 2 6 3 5
e 1 1 1 1
f 3 6 5 2
;
run;

proc sql;
  create table LOOP as
  select  distinct
          NAME
  from    DICTIONARY.COLUMNS
  where   LIBNAME="WORK"
    and   MEMNAME="TEST"
    and   NAME ne "emp_code"
  order by VARNUM;
quit;

data _null_;
  set loop end=last;
  if _n_=1 then call execute('proc sql;
                                create table WORK.WANT as
                                select  EMP_CODE,
                                        '||strip(name)||' as MAP_KEY,
                                        "'||strip(name)||'" as MTH_KEY
                                from    WORK.TEST');
  else do;
    call execute('union all
                  select  EMP_CODE,
                          '||strip(name)||' as MAP_KEY,
                          "'||strip(name)||'" as MTH_KEY
                  from    WORK.TEST');
  end;
  if last then call execute(';quit;');
run;

Solution
‎03-20-2014 06:01 AM
Trusted Advisor
Posts: 1,137

Re: want to convert data

Posted in reply to TarunKumar

Alternatively by proc transpose, i used format ord to sort the data as to get the desired output.

proc format;

    invalue ord 'Jan'=1

                'Feb'=2

                'Mar'=3

                'Apr'=4;

run;

proc sort data=test;

    by emp_code ;

run;

proc transpose data=test out=want(rename=(_name_=Map_code    col1=Mth_key));

    by emp_code ;

run;

data want_;

    set want;

    order=input(map_code,ord.);

run;

proc sort data=want_ out= want_y(drop=order);

    by order emp_code Mth_key map_code;

run;

Thanks,

Jag

Thanks,
Jag
Contributor
Posts: 29

Re: want to convert data

Posted in reply to TarunKumar

By transposing the data :-

proc sort data=test;

   by emp_code ;

run;

proc transpose data= test out=bug(rename= (_NAME_=Mth_key COL1=Map_code));

by emp_code;

var jan feb mar apr;

run;

data bug1;

set bug;

if Mth_key='Jan' then seq=1;

else if Mth_key='Feb' then seq=2;

else if Mth_key='Mar' then seq=3;

else if Mth_key='Apr' then seq=4;

run;

proc sort data=bug1;

by seq emp_code Map_code;

run;

in the next step drop seq.

🔒 This topic is solved and locked.

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

Discussion stats
  • 4 replies
  • 210 views
  • 0 likes
  • 5 in conversation