topic Re: Data transformation in SAS Programming

Data transformation

Aman4SAS — Sat, 09 Nov 2013 12:03:05 GMT

Dear friends looking for your help.

Kindly suggest:

Description

Pattern: A103,A104,A105,A106 belong to same person;

i need output data in form of

ID year month value_of_A103 value_of_A104 value_of_A105 value_of_A106

A103|A104|A105|A106 1976 M01 9.4 596104 5739058 6335162

there thousands of id in same pattern. kindly suggest

please find attached sample file.

Re: Data transformation

LinusH — Sat, 09 Nov 2013 14:01:21 GMT

So what is the pattern?

Axx3 - Axx6 is the same id?

I would say use the data step with conditional assigment and output statements.

Re: Data transformation

Aman4SAS — Sat, 09 Nov 2013 14:09:42 GMT

thanks for ur help sir,

yes i m using conditions but i m not getting proper result.

pattern is, always last digit will vary 3-6 and rest would be same for particular person.

kindly suggest.

Re: Data transformation

LinusH — Sat, 09 Nov 2013 15:02:03 GMT

Please attach your work so far, so we can make sugestions on improvements.

Re: Data transformation

Jagadishkatam — Sat, 09 Nov 2013 15:35:36 GMT

Please try the below code

data have;

infile "~\banktest.txt" dlm='09'x truncover firstobs=2;

input ID$ year Month$ value;

run;

proc sort data=have;

by year month;

run;

proc transpose data=have out=want(drop=_name_);

by year month;

id ID;

var value;

run;

proc transpose data=have(drop=value) out=variables;

by year month;

var id;

run;

data variables_;

set variables;

by year month;

id=catx('|',of col:);

if last.year;

keep year id;

run;

proc sql;

create table all as select a.*,b.id from want as a ,variables_ as b where a.year=b.year;

quit;

Thanks,

Jagadish

Re: Data transformation

art297 — Sat, 09 Nov 2013 15:41:15 GMT

Methinks that you have to get a better understanding of what your data represent and what is actually needed.

Your one example of what you indicated was a pattern appears to simply be grouped by month within year and that the new id variable you are trying to create is redundant.

Given that your data had a mixture of tab and space delimiters, I had to correct that in order to correctly read the data (although I'm sure there are other ways).

Does the following provide what you need?:

data have;

input @;

_infile_=translate(_infile_," ","09"x);

input ID $ year Month $ value;

cards;

A103 1976 M01 9.4

A103 1976 M02 9.3

A103 1976 M03 9.1

A103 1976 M04 9.0

A103 1976 M05 9.0

A104 1976 M01 596104

A104 1976 M02 588149

A104 1976 M03 581733

A104 1976 M04 578040

A104 1976 M05 579058

A105 1976 M01 5739058

A105 1976 M02 5759911

A105 1976 M03 5787128

A105 1976 M04 5818078

A105 1976 M05 5851090

A106 1976 M01 6335162

A106 1976 M02 6348060

A106 1976 M03 6368861

A106 1976 M04 6396118

A106 1976 M05 6430148

A113 1983 M01 8.4

A113 1983 M02 8.4

A113 1983 M03 8.3

A113 1983 M04 8.2

A113 1983 M05 8.1

A114 1983 M01 329594

A114 1983 M02 329039

A114 1983 M03 327840

A114 1983 M04 326067

A114 1983 M05 323786

A115 1983 M01 3581915

A115 1983 M02 3594082

A115 1983 M03 3612172

A115 1983 M04 3635918

A115 1983 M05 3665762

A116 1983 M01 3911509

A116 1983 M02 3923121

A116 1983 M03 3940012

A116 1983 M04 3961985

A116 1983 M05 3989548

A123 1983 M01 12.7

A123 1983 M02 12.7

A123 1983 M03 12.6

A123 1983 M04 12.5

A123 1983 M05 12.4

A124 1983 M01 282624

A124 1983 M02 282392

A124 1983 M03 281310

A124 1983 M04 279103

A124 1983 M05 275553

A125 1983 M01 1939561

A125 1983 M02 1940240

A125 1983 M03 1942942

A125 1983 M04 1947678

A125 1983 M05 1955098

A126 1983 M01 2222185

A126 1983 M02 2222632

A126 1983 M03 2224252

A126 1983 M04 2226781

A126 1983 M05 2230651

;

proc sort data=have;

by year month;

run;

proc transpose data=have out=want (drop=_:);

var value;

by year month;

id id;

run;

Re: Data transformation

Aman4SAS — Sat, 09 Nov 2013 15:42:10 GMT

"Throgh this i got my result , but as per my concern it doesn't look like profession and appropriate Kindly suggest me anything more short and appropriate with using macro"

applogy for trouble .

After importing table as work.test

data a(rename=(value=Value_A)) b(rename=(value=Value_B)) c(rename=(value=Value_C)) d(rename=(value=Value_D));

set test(keep=id year Month value);

if _n_=1 then do; flag=0; y=substr(id,1,3); end;

if y =substr(id,1,3) then do; y =substr(id,1,3);x="A"||substr(series_id,4,1);

if x="A3" then output a;

if x="A4" then output b;

if x="A5" then output c;

if x="A6" then output d;

end;

if y ne substr(id,1,3) then do; y =substr(id,1,3); flag=flag+1; x="A"||substr(id,4,1);

if x="A3" then output a;

if x="A4" then output b;

if x="A5" then output c;

if x="A6" then output d;

end;

retain flag y;

drop x y;

run;

data new(drop =flag);

merge a b c d;

by flag year month;

run;

Re: Data transformation

Aman4SAS — Sat, 09 Nov 2013 16:06:03 GMT

first of all i highly apology for my silly mistake which make trouble to import data.

Thanks for ur kind help,,, i believe its very helpful in my project.

but my desire output is to group all four [3-6 ] convert in or rename in one common id and rest value comes infront of that.

please check my effort n will wait for ur kind suggestion.

----------------------------

proc import datafile="C:\Users\loveislife\Desktop\banktest.txt" out =test dbms=dlm replace;

delimiter='09'x;

getnames=yes;

run;

data a(rename=(value=Value_A)) b(rename=(value=Value_B)) c(rename=(value=Value_C)) d(rename=(value=Value_D));

set test(keep=id year Month value);

if _n_=1 then do; flag=0; y=substr(id,1,3); end;

if y =substr(id,1,3) then do; y =substr(id,1,3);x="A"||substr(id,4,1);

if x="A3" then output a;

if x="A4" then output b;

if x="A5" then output c;

if x="A6" then output d;

end;

if y ne substr(id,1,3) then do; y =substr(id,1,3); flag=flag+1; x="A"||substr(id,4,1);

if x="A3" then output a;

if x="A4" then output b;

if x="A5" then output c;

if x="A6" then output d;

end;

retain flag y;

drop x y;

run;

data new(drop =flag);

merge a b c d;

by flag year month;

run;

-------------------------------

Re: Data transformation

Aman4SAS — Sat, 09 Nov 2013 16:07:35 GMT

sorry for one mistake in my last paste code,

kindly change series_id as id;

thanks

Re: Data transformation

art297 — Sat, 09 Nov 2013 17:41:12 GMT

You already have the result that you wanted .. with your own code too .. I'm impressed!

Another way of doing it might be:

data need (drop=_:);

length ID $4;

set have (rename=(id=_id));

ID=catt(substr(_id,1,3),input(substr(_id,4,1),1.)+6-input(substr(_id,4,1),1.));

type=byte(input(substr(_id,4,1),1.)+62);

run;

proc sort data=need;

by ID year month type;

run;

proc transpose data=need out=want (drop=_:) prefix=Value_;

var value;

by ID year month;

id type;

run;

Re: Data transformation

Aman4SAS — Sat, 09 Nov 2013 18:10:03 GMT

I have no words to thank you. its a reward for me that u have used impressed word for me.

Thanks a lot.