topic Re: sql with group by in SAS Procedures

sql with group by

Tal — Sun, 27 Jan 2013 20:11:28 GMT

hi guys

so i have a data like this below

Calendar_Day	Order_Status	fee	No
1/8/2013	Start	paid	1
1/8/2013	Start	paid	13
1/8/2013	Start	paid	1
1/8/2013	Start	paid	25
1/8/2013	Start	paid	1
1/8/2013	Start	free	2
1/8/2013	Start	free	2

i run a query that gives me something like this

1/8/2013	Start	Free	4
1/8/2013	Start	Paid	41

but sometimes the fee is always "paid" and i still need to get results that shows the "free" value of fee even though the count would be 0

1/8/2013	Start	Free	0
1/8/2013	Start	Paid	41

how do i achieve that,anyone please?

Re: sql with group by

Reeza — Sun, 27 Jan 2013 20:28:00 GMT

It sound like you want multiple variables in your group by clause.

proc sql;

create table want as

Select calendar_day, order_status, fee, sum(no) as total

from have

group by calendar_day, order_status, fee;

quit;

EDIT: You may want proc freq with the sparse option so you get missing groups showing up. proc freq data=have; table calendar_day*order_status*fee/out=want list sparse; run;

Re: sql with group by

Tal — Mon, 28 Jan 2013 04:57:26 GMT

Thanks for your response Reeza.The code you wrote is actually what i use to get this :

1/8/2013	Start	Free	4
1/8/2013	Start	Paid	41

but if lets say my dataset is:

1/8/2013	Start	paid	1
1/8/2013	Start	paid	13
1/8/2013	Start	paid	1
1/8/2013	Start	paid	25
1/8/2013	Start	paid	1

so no "free" in the entire dataset and i still want to get output such as :

1/8/2013	Start	Free	0
1/8/2013	Start	Paid	41

Is this is possible?

I know i can simpy add a dumb row with "free" and count=0 in the raw file,wont hurt my code/results or i can simply add it to the output which will be exported to excel but i was wondering if SAS can do this

I am trying to populate some tables with the data these queries will be giving me so the query results have to be consistent all the time.

Best

Re: sql with group by

Reeza — Mon, 28 Jan 2013 05:00:25 GMT

I realized that, that's what the edit suggestion is, proc freq with Sparse option.

Re: sql with group by

Tal — Mon, 28 Jan 2013 05:07:44 GMT

this is something new to me but will try it tomorrow after work and let you know how it goes.

Thanks Reeza!

Off topic:

i dont get the notifications for responses/replies to my postings.Any one knows why?

Re: sql with group by

Tal — Mon, 28 Jan 2013 05:12:47 GMT

sorry,real quick

if my dataset does not have the "free" at all then how this code :

proc freq data=have; table calendar_day*order_status*fee/out=want list sparse; run; will make the same appear in the results?

i am missing something?

Re: sql with group by

Jagadishkatam — Mon, 28 Jan 2013 05:12:48 GMT

Hi,

As per your post, if you consider that there is no row about 'free', then as per sql or with the proc freq or the data step we will not get any information in the output, because as per the given scenario the datastep or sql or procedure does have any other group in the data to display except the "paid" so only that information will be displayed. we need at least on row with "free" information in the data. if not even a single row is there in the data and if you want the missing category to be displayed then you need to insert the row as below;

proc sql;

create table want as

select Calendar_Day,order_status,fee, sum(no) as sum from have

group by Calendar_Day,order_status,fee;

insert into want set Calendar_Day='01AUG2013'd,Order_Status='start',fee='free';

quit;

Thanks,

Jagadish

Re: sql with group by

Tal — Mon, 28 Jan 2013 05:18:57 GMT

yea that makes sense.So i guess i can also use an if statement that would create me a duplicate record with fee="Free" and count=0 or maybe use something like your code does.

Thanks guys,

Re: sql with group by

Reeza — Mon, 28 Jan 2013 05:39:13 GMT

If you don't have a free at all then you can't add it in automatically using proc freq or proc sql. However if you have it for certain combinations but not all then the sparse option will add it in.

So assuming you're doing more than one day at a time and that the free will show up at least once during some group by instance then the proc freq with sparse option will add it in.

Re: sql with group by

Jagadishkatam — Mon, 28 Jan 2013 05:47:02 GMT

Yes, this method will create a row of free with missing no column Alternatively you can create new rows in the data step as well like below, consider that there are no rows with "free" in the datset

data want;

set have end=eof;

output;

if eof then do;

Calendar_Day='01AUG2013'd;

Order_Status='start';

fee='free';

no=.;

output;

end;

run;

Even i am unable to get the notification updates status, also the activity updates are also not showing up. May be there is a technical problem.

Thanks,

Jagadish

Re: sql with group by

Tal — Mon, 28 Jan 2013 12:18:58 GMT

yes,within a given week "free" will appear for sure so i will run both your codes guys and see how it goes.

@Jagadish ,i just noticed the " email notifications" on my profile was disabled so i enabled it back and i hope to get the notifications this time.

Thanks guys

Re: sql with group by

UrvishShah — Mon, 28 Jan 2013 13:48:01 GMT

Try this one...

%macro test;

proc sql;

select count(distinct fee) into :fees_group

from test;

quit;

proc sql;

create table test as

select calendar_day,order_status,fee,sum(no) as total

from total

group by 1,2,3;

quit;

%if %eval(&fees_group. EQ 0) %then %do;

proc sql;

insert into test

set calendar_day = 1/8/2013,

order_status = "start",

fee = "Free",

total = .;

quit;

%end;

%mend test;

%test;

Re: sql with group by

DBailey — Mon, 28 Jan 2013 14:04:34 GMT

If you create two static data sets that have all of the values you want:

data work.statuses;

order_status='Start';

output;

order_status='End';

output;

run;

data work.fees;

fee='paid';

output;

fee='free';

output;

run;

Then you can use proc sql to get all of the possible combinations:

proc sql;

select

m.calendar_day

,m.order_status

,m.fee

,sum(coalesce(h.no,0))as Total_No

from (

select distinct t1.calendar_day, t2.order_status,t3.fee

from work.have t1, work.statuses t2, work.fees t3) m

left outer join work.have h

on m.calendar_day=h.calendar_day

and m.order_status=h.order_status

and m.fee=h.fee

group by

m.calendar_day

,m.order_status

,m.fee;

quit;

produces

08JAN2013	End	free	0
08JAN2013	End	paid	0
08JAN2013	Start	free	4
08JAN2013	Start	paid	41

Re: sql with group by

Reeza — Mon, 28 Jan 2013 15:47:09 GMT

If you're not stuck on the format you could do something like the following:

proc sql;

create table want as

Select calendar_day, order_status, fee, sum((fee='paid')*no) as total_paid, sum((fee='free')*no) as total_free

from have

group by calendar_day, order_status;

quit;

Re: sql with group by

Tal — Mon, 28 Jan 2013 20:22:10 GMT

so the previous code you sent with the sparse function works perfectly Reeza,nice short and easy.No need to insert any additional records since the "free" appeares at least once within the week i am supposed to analyze .

So thanks again

Thanks to you all guys for your fancy codes as well

Re: sql with group by

Tal — Tue, 29 Jan 2013 00:52:26 GMT

hey Jagadish,

have a question about your code.When i replace '01AUG2013'd with today() for example,i still get the row inserted but the date is not right.You guys know why? Is today() a valid function within proc sql? I also tried with a macro variable

%let today = %sysfunc(today(),YYMMDD10.);

proc sql;

insert into mylib.week_new set Calendar_Day= &today,Order_Status='Start',Charge='Free',Reason='Other'; quit;

and still getting not the right date inserted

Re: sql with group by

Jagadishkatam — Tue, 29 Jan 2013 03:36:32 GMT

The today() function works well in the sql code as well as the datastep, i tried myself check the below code

proc sql;

create table want as

select Calendar_Day,order_status,fee, sum(no) as sum from have

group by Calendar_Day,order_status,fee;

insert into want set Calendar_Day=today(),Order_Status='start',fee='free';

quit;

Also,

data want;

set have end=eof;

output;

if eof then do;

Calendar_Day=today();

Order_Status='start';

fee='free';

no=.;

output;

end;

run;

Also, you can use &sysdate9. to get the today date, it is a macro variable for today date. I used it

proc sql;

create table want as

select Calendar_Day,order_status,fee, sum(no) as sum from have

group by Calendar_Day,order_status,fee;

insert into want set Calendar_Day="&sysdate9."d,Order_Status='start',fee='free';

quit;

Thanks,

Jagadish

Re: sql with group by

Tal — Tue, 29 Jan 2013 14:46:58 GMT

thanks Jagadish

will check it again when i get a moment

Re: sql with group by

Tal — Tue, 29 Jan 2013 19:29:40 GMT

actually today() worked from work did not work at home,..Weird

but thanks ,thank you!