Help using Base SAS procedures

sql with group by

Accepted Solution Solved
Reply
Super Contributor
Super Contributor
Posts: 440
Accepted Solution

sql with group by

hi guys

so i have a data like this below

Calendar_DayOrder_StatusfeeNo
1/8/2013   Startpaid1
1/8/2013       Startpaid13
1/8/2013    Startpaid1
1/8/2013   Startpaid25
1/8/2013       Startpaid1
1/8/2013   Startfree2
1/8/2013   Startfree2

i run a  query that gives me something like this

1/8/2013StartFree4
1/8/2013StartPaid41

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/2013StartFree0
1/8/2013StartPaid41

how  do i achieve that,anyone please?


Accepted Solutions
Solution
‎01-27-2013 03:28 PM
Super User
Posts: 18,491

Re: sql with group by

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;

View solution in original post


All Replies
Solution
‎01-27-2013 03:28 PM
Super User
Posts: 18,491

Re: sql with group by

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;

Super Contributor
Super Contributor
Posts: 440

Re: sql with group by

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

1/8/2013StartFree4
1/8/2013StartPaid41

but if lets say my dataset is:

1/8/2013   Startpaid1
1/8/2013       Startpaid13
1/8/2013    Startpaid1
1/8/2013   Startpaid

25

1/8/2013       Startpaid1

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

1/8/2013StartFree0
1/8/2013StartPaid41

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

T

Super User
Posts: 18,491

Re: sql with group by

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

Super Contributor
Super Contributor
Posts: 440

Re: sql with group by

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? Smiley Happy

Super Contributor
Super Contributor
Posts: 440

Re: sql with group by

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?

Super User
Posts: 18,491

Re: sql with group by

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.

Super Contributor
Super Contributor
Posts: 440

Re: sql with group by

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

Trusted Advisor
Posts: 1,137

Re: sql with group by

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

Thanks,
Jag
Super Contributor
Super Contributor
Posts: 440

Re: sql with group by

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,

Trusted Advisor
Posts: 1,137

Re: sql with group by

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

Thanks,
Jag
Super Contributor
Super Contributor
Posts: 440

Re: sql with group by

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

Trusted Advisor
Posts: 1,137

Re: sql with group by

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

Thanks,
Jag
Super Contributor
Super Contributor
Posts: 440

Re: sql with group by

thanks Jagadish

will check it again when i get a moment

Super Contributor
Super Contributor
Posts: 440

Re: sql with group by

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

but thanks  ,thank you! Smiley Happy

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 18 replies
  • 392 views
  • 14 likes
  • 5 in conversation