Creating a variable during a proc freq

[MeganE]

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

[Kurt_Bremser]

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.

[Astounding]

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;

[Reeza · Accepted solution]

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;

[MeganE]

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!

[ballardw]

Perhaps something like:

proc freq data=clms.allclaims_&year.;

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

run;