topic Re: how to control output of Subquery in proc sql; in SAS Enterprise Guide

how to control output of Subquery in proc sql;

deleted_user — Sat, 18 Apr 2009 08:13:08 GMT

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 .

Re: how to control output of Subquery in proc sql;

deleted_user — Sat, 18 Apr 2009 10:12:44 GMT

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;

Re: how to control output of Subquery in proc sql;

deleted_user — Sat, 18 Apr 2009 10:47:08 GMT

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 .

Re: how to control output of Subquery in proc sql;

deleted_user — Sat, 18 Apr 2009 10:49:22 GMT

i need one specific row's value in IN clause like

proc sql;
select * fom dim_account where account_id IN (5000002);
quit;

Re: how to control output of Subquery in proc sql;

LinusH — Mon, 20 Apr 2009 07:20:07 GMT

It's still unclear what you try to achieve, and why.
Please apply some more details and background to your problem.

/Linus

Re: how to control output of Subquery in proc sql;

deleted_user — Tue, 21 Apr 2009 06:31:01 GMT

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;