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?
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;
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;
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
T
I realized that, that's what the edit suggestion is, proc freq with Sparse option.
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?
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?
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.
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
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
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,
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
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
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 Jagadish
will check it again when i get a moment
actually today() worked from work did not work at home,..Weird
but thanks ,thank you!
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.