BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Ps8813
Fluorite | Level 6
Hi , i have a small and sweet question.
I want to change below code in proc sql.

Data test;
Set marks;
By stdid;
If first.stdid;
Run;

Marks
____________
Stdid. Marks
1. 34
1. 43
2. 54
2. 60
2. 65

Test
________________
Stdid. Marks
1. 34
2. 54

Thanks
1 ACCEPTED SOLUTION

Accepted Solutions
mohamed_zaki
Barite | Level 11
data have;
input stdid Marks;
cards;
1 34
1 43
2 54
2 60
2 65
;
run;

proc sql ;
select *
FROM  have h1
where marks=(select MIN(Marks) from have h2 where h1.stdid=h2.stdid)
order BY stdid;
quit;

View solution in original post

9 REPLIES 9
Reeza
Super User

Nope, doesn't exist. 😞

 

You need to find some other logic, how is your first record identified?

mohamed_zaki
Barite | Level 11
data have;
input stdid Marks;
cards;
1 34
1 43
2 54
2 60
2 65
;
run;

proc sql ;
select *
FROM  have h1
where marks=(select MIN(Marks) from have h2 where h1.stdid=h2.stdid)
order BY stdid;
quit;
Haikuo
Onyx | Level 15

Won't be the same if there is dups

data have;
input Stdid Marks;
cards;
1 34
1 34
1 43
2 54
2 60
2 65
;
mohamed_zaki
Barite | Level 11

If no more columns 

proc sql ;
select distinct *
FROM  have h1
where marks=(select MIN(Marks) from have h2 where h1.stdid=h2.stdid)
order BY stdid
;
quit;
Haikuo
Onyx | Level 15

At the risk of being obnoxious, what I was trying to say is not you can generate the same results, rather, is that your SQL logic is not to mirror 'first.', as you have heavily depended on some other assumptions. What if this:

data have;
input Stdid Marks;
cards;
1 36
1 34
1 43
2 54
2 60
2 65
;
mohamed_zaki
Barite | Level 11

Yes, i am heavily depend on the assumptions of marks ordered.

Anyway, @Ps8813 too much sweet is harmful!

Haikuo
Onyx | Level 15

I am second to @Reeza's comments, you can't get it done using Proc SQL without involving some 'extraordinary measures', I mean 'undocumented/unsupported feature', such as the following:

proc sql;
create table test as
select * from have
group by stdid
having monotonic()=min(monotonic())
;
quit;

monotonic() is not officially supported by SAS, use it at your own risk. However, this implementation is the closest I can think of to your data step counterpart 'first.'. 

 

Ps8813
Fluorite | Level 6

Thank u all for quick replying. Both below codes are working.

 

1:-

proc sql;
select * from test group by stdid having monotonic()=min(monotonic());
quit;

2:-

proc sql ;
select *
FROM test h1
where marks=(select MIN(Marks) from test h2 where h1.stdid=h2.stdid)
order BY stdid;
quit;

 

Though second solution wont give correct result when we have duplicate marks for same stdid.

 

Thanks again 🙂

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!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 9 replies
  • 2068 views
  • 1 like
  • 4 in conversation