topic Re: Combining data in SAS Programming

Combining data

NitinDwivedi — Fri, 20 Mar 2015 23:38:11 GMT

Hi,

Need help. I have 2 input datasets having following data.

Input dataset _1

area rating

1        0
1        1
1        2
1        3
1        4
1        5
2        0
2        1
2        2
2        3
2        4
2        5

Input Dataset -2

Area User-Id

1      G1
1      G2
2      G3
2      G4
2      G5

Output dataset need to have data in following way

OUTPUT DATASET

area user-id rating

1 G1 0
1 G1 1
1 G1 2
1 G1 3
1 G1 4
1 G1 5
1 G2 0
1 G2 1
1 G2 2
1 G2 3
1 G2 4
1 G2 5
2 G3 0
2 G3 1
2 G3 2
2 G3 3
2 G3 4
2 G3 5 and so on..

Thanks in advance.

Regards,

Nitin

Re: Combining data

Patrick — Sat, 21 Mar 2015 00:00:32 GMT

Looks like homework to me. What have you already done? Post your code also if it's not working yet.

Hint: Proc SQL, join using column "area"

You're asked in this exercise to combine data from two tables which have a many:many relationship. This is a case where a SAS datastep merge and a SQL join will return different results. It is very important for you to understand the difference and why this happens. And the best way of learning SAS coding is "to code".

Re: Combining data

NitinDwivedi — Sat, 21 Mar 2015 00:41:14 GMT

Thanks Patrick.

Your hint helped me to resolve the issue. Actually I am new to SAS, therefore small things are quite big for me. I had not thought of using SQL at all and was looking from SET/merge perspective, though there may be solution in that way too, but I was struggling to find the solution.

PROC SQL;

CREATE TABLE ABCD AS

SELECT *

FROM ABC, XYZ

WHERE ABC.A =

XYZ.A;

QUIT;

PROC SORT DATA=ABCD NODUPKEY;

BY LR B;

RUN;

Re: Combining data

Patrick — Sat, 21 Mar 2015 06:25:36 GMT

Whenever you want to create all combinations of rows coming from 2 tables (or all combinations with matching keys) and the relationship between the tables is many:many then use Proc SQL.

Not sure why you need a Proc Sort Nodupkey in the code you've posted - but it appears to be dealing with slightly different data than what you've posted as sample.

data have1;

infile datalines dlm=' ' truncover;

Input area rating;

datalines;

1 0

1 1

1 2

1 3

1 4

1 5

2 0

2 1

2 2

2 3

2 4

2 5

;

run;

data have2;

infile datalines dlm=' ' truncover;

Input Area User_Id $;

datalines;

1 G1

1 G2

2 G3

2 G4

2 G5

;

run;

proc sql;

create table want as

select r.area, r.user_id, l.rating

from have1 l, have2 r

where l.area=r.area

;

quit;