topic Re: Application of an Numeric Format in SAS Programming

Application of an Numeric Format

JasonL — Fri, 06 Jun 2014 19:57:41 GMT

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;

Re: Application of an Numeric Format

Reeza — Fri, 06 Jun 2014 20:09:07 GMT

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;

format j pswwkfmt.;

run;

proc univariate data=test;

by k j;

var i;

output out=test2 sum=sum;

run;

Re: Application of an Numeric Format

JasonL — Fri, 06 Jun 2014 20:33:41 GMT

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.

Re: Application of an Numeric Format

Reeza — Fri, 06 Jun 2014 20:37:05 GMT

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;

Re: Application of an Numeric Format

JasonL — Fri, 06 Jun 2014 20:47:27 GMT

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

Re: Application of an Numeric Format

Reeza — Fri, 06 Jun 2014 21:39:38 GMT

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;

Re: Application of an Numeric Format

JasonL — Mon, 09 Jun 2014 13:15:42 GMT

Thanks a lot Reeza!