topic Re: Question with ordering variables in SAS Programming

Question with ordering variables

GregBond — Fri, 21 Mar 2014 13:45:36 GMT

Hi all,

I have created a variable 'healthhad'. When running the proc freq procedure, I would like the response options to appear in the order as the code is written.

I have tried numerous order=formatted/data/freq/internal statements, however these do not work. Any suggestions? Thanks!

data new/view=new;

set work.PWL_SPSS ;/*CREATING "TYPE OF HEALTH INSURANCE DID YOU HAVE"*/

length healthhad $44;

keep healthhad;

if q101r1 then do;

healthhad='Private insurance that is provided by your current or former employer or union' ;

output;

end;

if q101r2 then do;

healthhad=' Insurance that is provided through a family members employer or union' ;

output;

end;

if q101r3 then do;

healthhad='Private insurance that was purchased directly from an insurance company' ;

output;

end;

if q101r4 then do;

healthhad='Medicare, for people 65 and older' ;

output;

end;

if q101r5 then do;

healthhad=' Medicare, for people with certain disabilities' ;

output;

end;

if q101r6 then do;

healthhad='Medicare supplemental insurance ';

end;

if q101r7 then do;

healthhad='Medicaid' ;

output;

end;

if q101r8 then do;

healthhad='TRICARE' ;

output;

end;

if q101r9 then do;

healthhad='Veterans Affairs' ;

output;

end;

if q101r10 then do;

healthhad=' Indian Health Service' ;

output;

end;

if q101r11 then do;

healthhad='I did not have health insurance' ;

output;

end;

if q101r12 then do;

healthhad='Do not know/Not sure' ;

output;

end;

proc freq data=new order=data;

tables healthhad /nocum nocol;

run;

proc freq data=new order=data;

tables healthhad /nocum nocol;

run;

Re: Question with ordering variables

RichardinOz — Fri, 21 Mar 2014 14:17:49 GMT

Proc Freq does not appear to have the flexibility you require to preserve a particular order in summary reporting. Tabulate, Means and Report all allow ordering by a prearranged format. To implement tis you need to create the format using the NOTSORTED option , and then report specifying both ORDER=DATA and PRELOADFMT options.

See Base SAS(R) 9.2 Procedures Guide

I would suggest creating the healthhad format as

Proc Format ;

Value $healthhad NOTSORTED

'q101r1' = 'Private insurance that is provided by your current or former employer or union'

'q101r2' = ' Insurance that is provided through a family members employer or union'

[etc]

Then code your data using coded values for the column helathhad

Data want ;

Set have ;

format healthhad $healthhad. ;

Select ;

When q101r1 then healthhad = 'q101r1' ;

When q101r2 then healthhad = 'q101r2' ;

[etc]

Otherwise ;

end ;

...

Run;

Then use Proc Tabulate or Report to count and report the values.

Richard

Re: Question with ordering variables

ballardw — Fri, 21 Mar 2014 14:51:48 GMT

An alternate approach would be to code the variable healthhad as numeric in the order you wish them to appear and use the Order=Internal option on proc freq statement.

Array processing would simplify the coding as well

Array Q101 Q101r: ; /* assumes all of the Q101rx variables are to be recoded*/

Do _i_ = 1 to dim (Q101);

if q101[_i_] then healthhad = _i_;

end;

Since questionaires often have a response section like:

1. Private insurance that was purchased directly from an insurance company

2. Insurance that is provided through a family members employer or union

etc

it is easy to do a copy and paste to make a custom format

proc format;

value HealthHad

1= 'Private insurance that was purchased directly from an insurance company'

2= 'Insurance that is provided through a family members employer or union'

;

run;

Done this say more times than I want to think about.

By the way, the Format approach has an added bonus. You don't have to add variables if you later need to group your responses such as

1,2 and 3 to indicate private insurance, or 4,5,6 as medicare:

Proc format;

value HeathInsGroup

1,2,3 = 'Private insurance'

4,5,6 = 'Medicare'

;

Then you only have to change the format for analysis. The formatted group values will also work when used as categorical variables in most analysis procedures.

I strongly recommend at least trying this a few times.

And a later thought, some survey software I have used has options for exporting variables and single choice multiple response questions like this could be exported in a number of manners. One defaulted to multiple dichotomous variables such as you show but there was an option to export a single variable with the choice value. It may be too late for this project but something to bring up with your data collection side. There really shouldn't be a need to do as much work like this if the software will do it automatically. Your data set name makes me believe it went through SPSS at some point. If the data were exported as I suggest it could be possible for SPSS to export the data AND generate a SAS program file for formats of the value labels. Can't hurt to explore this

Re: Question with ordering variables

RW9 — Fri, 21 Mar 2014 14:59:47 GMT

Can you not put the order of variables into a macro variable as you go along, then datastep retain &m_variable. after the proc freq? E.g. (not tested)

...

retain col_ord;

if q101r1 then do;

healthhad='Private insurance that is provided by your current or former employer or union' ;

output;

end;

col_ord =strip(col_ord)||" "||"q101r1";

if q101r2 then do;

healthhad=' Insurance that is provided through a family members employer or union' ;

output;

end;

col_ord =strip(col_ord)||" "||"q101r1";

if last record then call symput('COL_ORD',strip(col_ord);

...

proc freq;

...

data want;

set freq;

retain &col_ord;

run;

Re: Question with ordering variables

GregBond — Fri, 21 Mar 2014 15:05:11 GMT

Hi Richard,

Thank you for your insightful responses. I will look over each and get back to as to which works the best. Thanks again, as you have been a huge help!

Regards

Re: Question with ordering variables

Astounding — Fri, 21 Mar 2014 16:54:51 GMT

There are a number of good ideas already posted here. One simple possibility would be to change the value of healthhad, such as:

length healthhad $ 49;

if q101r1 then do;

healthhad='r01: Private insurance that is provided by your current or former employer or union' ;

output;

end;

If that's an acceptable approach, it should be easy to implement.