BookmarkSubscribeRSS Feed
MLS
Calcite | Level 5 MLS
Calcite | Level 5
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.
5 REPLIES 5
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
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
MLS
Calcite | Level 5 MLS
Calcite | Level 5
Please notice that column headers from table1 to table 2 are different.
RickM
Fluorite | Level 6
couldn't you just join table1 where color=red to table1 where color=blue?
MLS
Calcite | Level 5 MLS
Calcite | Level 5
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;
Patrick
Opal | Level 21
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

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!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 5 replies
  • 776 views
  • 0 likes
  • 4 in conversation