BookmarkSubscribeRSS Feed
JasonL
Quartz | Level 8

In running the following code, I was hoping to sum "PSW_Hrs" by "PSW_Hrs_Per_Week" categories as defined in the proc format.  It turned out, however, some categories ('5<-7 Hrs' and '7<-21 Hrs', specifically) appeared twice in the output.  Why and is there a solution without first creating a grouping variable in a data step?

Thanks.
Jason

proc FORMAT;

            VALUE PSWWkFmt

                        .,0        = 'No Hr'

                        0<-1 = '0<-1 Hr'

                        1<-2 = '1<-2 Hrs'

                        2<-5 = '2<-5 Hrs'

                        5<-7 = '5<-7 Hrs'

                        7<-21 = '7<-21 Hrs'

                        21<-56 = '21<-56 Hrs'

                        56<-high = '>56 Hrs';

run;

proc sort data=MyData; by LHIN_Code PSW_Hrs_Per_Week; run;

proc univariate data=MyData;

            var PSW_Hrs;

            output sum=Sum_PSW_Hrs out=PSW_Hrs_By_User_Wk;

            by LHIN_Code PSW_Hrs_Per_Week;

            format PSW_Hrs_Per_Week PSWWkFmt.;

run;


6 REPLIES 6
Reeza
Super User

It shouldn't...

Is your data sorted properly? Can you post the log and/or sample data that will replicate the problem.

Does the following work as expected:

proc FORMAT;

            VALUE PSWWkFmt

                        .,0        = 'No Hr'

                        0<-1 = '0<-1 Hr'

                        1<-2 = '1<-2 Hrs'

                        2<-5 = '2<-5 Hrs'

                        5<-7 = '5<-7 Hrs'

                        7<-21 = '7<-21 Hrs'

                        21<-56 = '21<-56 Hrs'

                        56<-high = '>56 Hrs';

run;

data test;

    do k=1 to 3;

    do i=1 to 100;

    j=i;

    output;

    end;

    end;

    format j pswwkfmt.;

run;

proc univariate data=test;

    by k j;

    var i;

    output out=test2 sum=sum;

run;

JasonL
Quartz | Level 8

Thanks Reeza!  Your code works as expected after adding a format statement in the proc univariate:

proc univariate data=test;

    by k j;

    var i;

    output out=test2 sum=sum;

    format j pswwkfmt.;

run;

Some of the categories from my code appeared twice and not in sequence, for some reason.  I still can't figure out how to correct.

Reeza
Super User

Ok. I'll assume that its not the different LHIN that's confusing you.

Then you create a duplicate of your variable and run a proc freq of it. one with a format and one without to see where its not working. BY LHIN if you need to.

data test;

set have;

new_var=old_var;

format new_var pswwkfmt.;

run;

proc freq data=test;

table new_var*old_var/list;

run;

JasonL
Quartz | Level 8

Here is a sample of the data that I was try to proc freq on.

Reeza
Super User

It looks like it has to do with the number of decimal points in your number. So I'd consider rounding to 0.001 or something you consider reasonable and then applying the format.

I still wouldn't expect this behaviour though and would consider reporting it to SAS to see what they have to say.

data have;

    set jl.sample;

    new_var=round(PSW_Hrs_Per_Week, 0.001);

    format new_var pswwkfmt.;

run;

   

proc univariate data=have;

            var PSW_Hrs;

            output sum=Sum_PSW_Hrs out=PSW_Hrs_By_User_Wk;

            by LHIN_Code new_var;

            format PSW_Hrs_Per_Week PSWWkFmt.;

run;

JasonL
Quartz | Level 8

Thanks a lot Reeza!

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!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 6 replies
  • 588 views
  • 6 likes
  • 2 in conversation