topic Proc SQL in SAS Procedures

Proc SQL

MLS — Fri, 16 Apr 2010 14:38:23 GMT

Hi. I have a large SAS dataset and I am using Proc SQL. This is a sample data set from table1. How can I get table 2 from table1 to have one unique record for each person:
Table 1
Name Color Age Enrolled
Jo red 24 Y
Jo blue 24 Y
Ray red 31 Y
MIke blue 28 N

Table 2
Name Red Blue Age Enrolled
Jo red blue 24 Y
Ray red 31 Y
Mike blue 28 N

Thanks.

Re: Proc SQL

sbb — Fri, 16 Apr 2010 14:42:54 GMT

Use DISTINCT with your SELECT and column/variable list.

Scott Barry
SBBWorks, Inc.

Suggested Google advanced search argument, this topic/post:

proc sql select distinct site:sas.com

Re: Proc SQL

MLS — Fri, 16 Apr 2010 14:51:09 GMT

Please notice that column headers from table1 to table 2 are different.

Re: Proc SQL

RickM — Fri, 16 Apr 2010 15:07:47 GMT

couldn't you just join table1 where color=red to table1 where color=blue?

Re: Proc SQL

MLS — Fri, 16 Apr 2010 15:27:16 GMT

Is this the query you suggest? (Table2 has 2 columns that Table1 doesn't have them)

proc sql;

create table work.Table2 as
(Select distinct
a.Name,
a.Age,
a.Enrolled,
Red,
Blue
from work.Table1 a join work.Table1 a
if a.Color = 'red' then Red = 'red'
else (if a.Color = 'blue' then Blue = 'blue') end if );
quit;

Re: Proc SQL

Patrick — Sun, 18 Apr 2010 11:40:29 GMT

Hi

I believe the code below does what you're looking for:

data have;
infile datalines truncover;
input Name $ Color $ Age Enrolled $ ;
datalines;
Jo red 24 Y
Jo blue 24 Y
Ray red 31 Y
MIke blue 28 N
;
run;
proc sql;
select
name
,max(case(color) when ('blue') then color else '' end) as blue
,max(case(color) when ('red') then color else '' end) as red
,age
,enrolled
from have
group by name,age,enrolled
;
quit;

This example covers the cases 'red' and 'blue' - but what if one doesn't know for sure what colours will be in the data.

I thinks this is a case where an approach using PROC TRANSPOSE would allow a more dynamic solution.

HTH
Patrick