topic Re: Creating a variable during a proc freq in SAS Programming

Creating a variable during a proc freq

MeganE — Tue, 07 Mar 2017 19:33:14 GMT

Hi,

I'd like to covert this to a proc freq for efficiency, but i can't seem to find code to be able to create a variable at the same time. For all i know it's not allowed. But i thought i'd ask. I can rename stuff, keep stuff, use a where statement. But i can't seem to find code to create a variable.

Convert...

        proc sql;
           create table junk.qc_hcfasaf_&year. as
           select &year. as year,
               hcfasaf,
               count(hcfasaf) as count
           from clms.allclaims_&year.
           group by hcfasaf;
       quit;

to....

proc freq data=clms.allclaims_&year.;

tables hcfasaf / out=junk.qc_hcfasaf_&year.;

run;

But i don't know if i can create a variable during the step.

Anyone?

Thanks
Megan

Re: Creating a variable during a proc freq

Kurt_Bremser — Tue, 07 Mar 2017 19:37:04 GMT

Aside from variables created by procedures themselves, no new variables can be created. That needs to be done in a data or proc sql step.

Re: Creating a variable during a proc freq

Astounding — Tue, 07 Mar 2017 19:51:05 GMT

While PROC FREQ doesn't support this, here are a few ideas to consider.

Do you really need a variable, or will some other version of the year be sufficient? For example, YEAR could become part of both the data set label and the variable label:

proc freq data=clms.allclaims_&year.;

tables hcfasaf / out=junk.qc_hcfasaf_&year. (label="Counts for &year");

label hcfasaf = "hcfasaf for &year";

run;

Just be sure to use double quotes, not single quotes.

If you must have a variable, you can add it temporarily to a VIEW to speed up the processing:

data temp / view=temp;

set clms.allclaims_&year.;

year = &year;

run;

proc freq data=temp;

tables year * hcfasaf / out=junk.qc_hcfasaf_&year.;

run;

Re: Creating a variable during a proc freq

Reeza — Tue, 07 Mar 2017 20:48:51 GMT

No you can't create a new variable in proc freq.

But depending on your overall goal there may be another way. It looks like you'll be running this for multiple years?

I would suggest when you append the final datasets you can extract the year from the table names using the INDSNAME option.

data want;
set qc_hcfasaf_2001-qc_hcfasaf_2016 indsname = source;

year = scan(source, -1, "_");

run;

Re: Creating a variable during a proc freq

ballardw — Tue, 07 Mar 2017 20:50:52 GMT

Perhaps something like:

proc freq data=clms.allclaims_&year.;

tables &year*hcfasaf / norow nocol out=junk.qc_hcfasaf_&year. (rename=(&year = year));

run;

Re: Creating a variable during a proc freq

MeganE — Thu, 09 Mar 2017 14:24:35 GMT

This is brilliant! I've never heard of indsname before.

Yes, it'll be running 16 times and i'd rather have one final table than 16, but i need to tell the years apart. 🙂

Thanks!