BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Tal
Pyrite | Level 9 Tal
Pyrite | Level 9

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?

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

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

18 REPLIES 18
Reeza
Super User

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;

Tal
Pyrite | Level 9 Tal
Pyrite | Level 9

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

Reeza
Super User

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

Tal
Pyrite | Level 9 Tal
Pyrite | Level 9

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

Tal
Pyrite | Level 9 Tal
Pyrite | Level 9

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?

Reeza
Super User

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.

Tal
Pyrite | Level 9 Tal
Pyrite | Level 9

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

Jagadishkatam
Amethyst | Level 16

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
Tal
Pyrite | Level 9 Tal
Pyrite | Level 9

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,

Jagadishkatam
Amethyst | Level 16

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
Tal
Pyrite | Level 9 Tal
Pyrite | Level 9

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

Jagadishkatam
Amethyst | Level 16

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
Tal
Pyrite | Level 9 Tal
Pyrite | Level 9

thanks Jagadish

will check it again when i get a moment

Tal
Pyrite | Level 9 Tal
Pyrite | Level 9

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

but thanks  ,thank you! Smiley Happy

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!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 18 replies
  • 1513 views
  • 14 likes
  • 5 in conversation