BookmarkSubscribeRSS Feed
deleted_user
Not applicable
Hi Reader,

I have one query like

proc sql;
select *
from dim_account
where account_id in ( select distinct account_id
from dim_account
)
quit;

i can control the out put of main query in terms of no of observation to be displayed. but now here how can i control the o/p of subquery.
in dim_account various account_id are availble and each distinct accout_id have various rows.
the output of subquery may be many account_id but i want only first row as output of subquery. so that all rows belongs single account_id will be displayed.


Hint: we use (Firstobs=1 obs=1) while reading value from data set .
5 REPLIES 5
deleted_user
Not applicable
Hello Arvind,

I'm not too sure whether I understood your problem. Are you trying to identify records with no duplicate keys. If so ... this might help. It uses a nice feature of SAS called "query remerging". You can, of course, use this feature in a subquery.

data T01_input;
do i=1 to 1000;
x=int(ranuni(0)*300);
output;
end;
run;

proc sql;
create table T02_output as
select *
from T01_input
group by x
having count(*)=1;
run;
deleted_user
Not applicable
Hi,

thanks for reply...

just the pros sql step which i wrote..in this output of subquery may be more than one row. but i want only specific row .
deleted_user
Not applicable
i need one specific row's value in IN clause like

proc sql;
select * fom dim_account where account_id IN (5000002);
quit;
LinusH
Tourmaline | Level 20
It's still unclear what you try to achieve, and why.
Please apply some more details and background to your problem.

/Linus
Data never sleeps
deleted_user
Not applicable
I may be missing your intent, but the subquery is a fully formed query in its own right.
You can add any conditional clauses to it (the subquery) that you like.

proc sql;
select *
from dim_account
where account_id in ( select distinct account_id
from dim_account
WHERE somecondition is true)
quit;

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!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

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
  • 924 views
  • 0 likes
  • 2 in conversation