DATA Step, Macro, Functions and more

Can any one help me below issue either macro or datastep should be fine

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 5
Accepted Solution

Can any one help me below issue either macro or datastep should be fine

  1. Table A.pngTable ATable B.pngTable BTable C.pngTable c

I have Table A and Table B , I want desired output should be like Table C

Either Macro or datastep should be fine

thanks in advance

 


Accepted Solutions
Solution
‎04-18-2018 12:47 AM
Super User
Posts: 10,784

Re: Can any one help me below issue either macro or datastep should be fine

Posted in reply to Bhargavi221

Post the sas code, NOT picture, nobody would like to type it for you if want help from others.

 

data a;
input id count dty;
cards;
1022 2 200
1022 2 600
6500  3  3
6500  3 50
6500  3 12
;
data b;
input id dt : date9.;
format dt date9.;
cards;
1022 25apr2018
1022 11jul2018
6500  08may2018
6500  31may2018
6500  20mar2018
;
data have;
 merge a b;
 by id;
run;
proc sql noprint;
select max(count) into : n
 from (select count(*) as count from have group by id);
quit;
data want;
 set have;
 by id;
 array dty_{&n};
 array dt_{&n};
 if first.id then n=0;
 n+1;dty_{n}=dty;dt_{n}=dt;
drop dt n;
format dt_: date9.;
run;

View solution in original post


All Replies
Contributor
Posts: 47

Re: Can any one help me below issue either macro or datastep should be fine

Posted in reply to Bhargavi221

Hi 

It can be solved, but I have a couple of questions.

 

  1. I do not understand why the COUNT field in data set C is changes from 3 to 2 in the first two rows with ID 650. 
  2. As there is no Unique ID, we must presume that the data sets A and B are in the same order. Otherwise is is not possible to pair dates and quantities within the same ID group,  eg. the quantity 50 to the date 31may2018.
  3. Do you really want the numbered quantity/date pairs in C to respect the input data order, so 20mar2018 goes in pair 3, after the dates in May?
Occasional Contributor
Posts: 5

Re: Can any one help me below issue either macro or datastep should be fine

Posted in reply to ErikLund_Jensen

1.actuvally the count is 3 only by mistake it's edited to 2

2.please consider dataset a and b are same order

3.yes i want the numbered  quantity/date pairs go to the respective to input dataset order

Solution
‎04-18-2018 12:47 AM
Super User
Posts: 10,784

Re: Can any one help me below issue either macro or datastep should be fine

Posted in reply to Bhargavi221

Post the sas code, NOT picture, nobody would like to type it for you if want help from others.

 

data a;
input id count dty;
cards;
1022 2 200
1022 2 600
6500  3  3
6500  3 50
6500  3 12
;
data b;
input id dt : date9.;
format dt date9.;
cards;
1022 25apr2018
1022 11jul2018
6500  08may2018
6500  31may2018
6500  20mar2018
;
data have;
 merge a b;
 by id;
run;
proc sql noprint;
select max(count) into : n
 from (select count(*) as count from have group by id);
quit;
data want;
 set have;
 by id;
 array dty_{&n};
 array dt_{&n};
 if first.id then n=0;
 n+1;dty_{n}=dty;dt_{n}=dt;
drop dt n;
format dt_: date9.;
run;
☑ This topic is solved.

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

Discussion stats
  • 3 replies
  • 171 views
  • 0 likes
  • 3 in conversation