Data transformation

[Aman4SAS]

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.

[LinusH]

So what is the pattern?

Axx3 - Axx6 is the same id?

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

[Aman4SAS]

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.

[LinusH]

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

[Aman4SAS]

"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;

[Aman4SAS]

sorry for one mistake in my last paste code,

kindly change series_id as id;

thanks

[Jagadishkatam]

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

[art297]

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;

[Aman4SAS]

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;

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

[art297 · Accepted solution]

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;

[Aman4SAS]

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

Thanks a lot.