SAS code join data

Accepted Solution Solved
Reply
Contributor
Posts: 31
Accepted Solution

SAS code join data

Hi ,

I have a dataset like A1  and i need to create A2

 

can anyone pls let me know how to achieve below task . When

 

id is 2 then i need to pick item02 and price02 values and also in same line

 

when id is 3 i need to pick item03 and price03 from same line

 

A1

 

iditempriceitem01price01item02price02item03price03item04price04
267506.51010.251000.251000.25
243506.51023.251003.252053.25
3256032.51038.52504.23331.25
4925622.54452.26500.355060.32

 

A2

iditempriceitem01price01item02price02item03price03item04price04fi_itemfi_price
267506.51010.251000.251000.251010.25
243506.51023.251003.252053.251023.25
3256032.51038.52504.23331.252504.2
4925622.54452.26500.355060.325060.32

Accepted Solutions
Solution
‎06-15-2016 05:09 PM
Occasional Contributor
Posts: 17

Re: SAS code join data

proc sql noprint;

 

select max(ID) into: IMAX from A1;

 

quit;

 

data A2;

 

set A1;

 

do i=1 to &IMAX.;

 

if ID eq &i then do;

 

FI_ITEM = item0&i.;

 

FI_PRICE=PRICE0&i;

 

end;

 

end;

 

run;

 

 

 

(Code is not tested on SAS, please try and modify as per your requirements)

View solution in original post


All Replies
Solution
‎06-15-2016 05:09 PM
Occasional Contributor
Posts: 17

Re: SAS code join data

proc sql noprint;

 

select max(ID) into: IMAX from A1;

 

quit;

 

data A2;

 

set A1;

 

do i=1 to &IMAX.;

 

if ID eq &i then do;

 

FI_ITEM = item0&i.;

 

FI_PRICE=PRICE0&i;

 

end;

 

end;

 

run;

 

 

 

(Code is not tested on SAS, please try and modify as per your requirements)

Contributor
Posts: 31

Re: SAS code join data

I have executed below code but i dont see   FI_ITEM   and FI_PRICE columns in end dataset can u pls let me know why and how should i change my code

 

 

 

Thanks

 

 

 

 

%macro sale;


proc sql noprint;

 

select max(ID) into: IMAX from A1;

 

quit;

 

data A2;

 

set A1;

 

%do i=1 to &IMAX.;

 

%if ID eq &i. %then %do;

 

FI_ITEM = item0&i.;

 

FI_PRICE=PRICE0&i.;

 

%end;

 

%end;

 

run;
%mend;
%sale;

Contributor
Posts: 31

Re: SAS code join data

Hi ,

 

I am wondering why does the above code doesnot create  FI_ITEM ,

 

FI_PRICE does anyone have any idea

Grand Advisor
Posts: 17,325

Re: SAS code join data


jhh197 wrote:

I have executed below code but i dont see   FI_ITEM   and FI_PRICE columns in end dataset can u pls let me know why and how should i change my code

 

 

 

Thanks

 

 

 

 

%macro sale;


proc sql noprint;

 

select max(ID) into: IMAX from A1;

 

quit;

 

data A2;

 

set A1;

 

%do i=1 to &IMAX.;

 

%if ID eq &i. %then %do;

 

FI_ITEM = item0&i.;

 

FI_PRICE=PRICE0&i.;

 

%end;

 

%end;

 

run;
%mend;
%sale;


That's not going to work if ID = 10 and isn't the most efficient solution. I would highly recommend the array method or VValuex instead of macro variables. Your adding a level of complexity that's not required and makes your code harder to understand and maintain for a very simple requirement. 

@jhh197

Additonally please quote responses, otherwise we don't know which response lines up. 

Contributor
Posts: 31

Re: SAS code join data

Thanks a lot . I got it

Respected Advisor
Posts: 4,963

Re: SAS code join data

You'll need to know how many items and prices  you have, and hard-code that number.  Other than that, this is a straightforward application for arrays:

 

data A2;

set A1;

array items {50} item01-item50;

array prices {50} price01-price50;

if (1 <= id <= 50) then do;

   fi_item = items{id};

   fi_price = prices{id};

end;

run;

Contributor
Posts: 31

Re: SAS code join data

I was able to get the output using this .

 

 

 

Thanks

Contributor
Posts: 31

Re: SAS code join data

Thank you  so much

Grand Advisor
Posts: 17,325

Re: SAS code join data

Use the VVALUEX() function. 

 

For example:

 

Fi_item= vvaluex('item'||put(id, z2.));

 

 

Grand Advisor
Posts: 9,571

Re: SAS code join data

 

 

 

 

data have;
infile cards truncover expandtabs ;
input id	item	price	item01	price01	item02	price02	item03	price03	item04 price04;
cards;
2	6	7	50	6.5	101	0.25	100	0.25 100 0.25
2	4	3	50	6.5	102	3.25	100	3.25 205 3.25
3	2	5	60	32.5	 103	 8.5	 250	 4.2 333 1.25
4	9	2	56	22.5	445	2.2	650	0.35 506 0.32
;
run;
data want;
 set have;
 array i{*} item01-item04;
 array p{*} price01-price04;
 fi_item=i{id};
 fi_price=p{id};
run;
☑ This topic is SOLVED.

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

Discussion stats
  • 10 replies
  • 489 views
  • 0 likes
  • 5 in conversation