BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Aman4SAS
Obsidian | Level 7

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.

1 ACCEPTED SOLUTION

Accepted Solutions
art297
Opal | Level 21

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;

View solution in original post

10 REPLIES 10
LinusH
Tourmaline | Level 20

So what is the pattern?

Axx3 - Axx6 is the same id?

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

Data never sleeps
Aman4SAS
Obsidian | Level 7

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
Tourmaline | Level 20

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

Data never sleeps
Aman4SAS
Obsidian | Level 7

"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
Obsidian | Level 7

sorry for one mistake in my last paste code,

kindly change series_id as id;

thanks

Jagadishkatam
Amethyst | Level 16

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

Thanks,
Jag
art297
Opal | Level 21

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
Obsidian | Level 7

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
Opal | Level 21

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
Obsidian | Level 7

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

Thanks a lot.

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
  • 10 replies
  • 1024 views
  • 3 likes
  • 4 in conversation