Running the same proc SQL on list of similar variables

user927 — Wed, 10 Jul 2019 20:32:01 GMT

Hello,

I am attempting to create individual descriptive tables for a number of binary variables in my dataset. I've created the below proc sql code to generate the tables in the way that I would like them to look (see attached image for view of resulting table used for a binary variable named 'offerhdhp_any'). They need to include the variable name, variable label, value format, variable value, count (unweighted), count (weighted), percent (unweighted), and percent (weighted).

I would like to generate this table for a list of similar binary variables in my dataset. Is there a way for this proc sql code to reference a list of variables instead of one single variable? If the answer is to use arrays, I would benefit from an example of how to use array within proc sql.

Thank you very much for your help and your time.

Proc sql;
Create table temp2 as 
Select "OfferHdhp_Any" as VariableName, OfferHdhp_Any as ValueFormat, OfferHdhp_Any as VarValue format=3.0, 
count(*) as N_Unweighted format=comma10., sum(firmwgt) as N_Weighted format=comma10., 
count(OfferHdhp_Any)/(select count(OfferHdhp_Any) from temp)*100 as Percent_Unweighted /*format=percent8.2*/
From temp
Group by OfferHdhp_Any;
Create table temp3 as
Select VariableName, _LABEL_ as VariableLabel, ValueFormat, VarValue,-cmiss(VarValue)+1 as ValuePresent, N_Unweighted, N_Weighted, Percent_Unweighted 
From temp2
Inner join temp_labels
On temp2.VariableName=temp_labels._NAME_
order by VarValue; 
Create table temp4 as
Select VariableName, VariableLabel, ValueFormat, VarValue, N_Unweighted, N_Weighted, Percent_Unweighted, 
ValuePresent*N_Weighted / (sum(ValuePresent * N_Weighted))*100 as Percent_Weighted 
From temp3;
quit;

proc print data=temp4; run;

Re: Running the same proc SQL on list of similar variables

Reeza — Wed, 10 Jul 2019 20:37:27 GMT

Can you convert your code to proc means instead? You cannot use an array within SQL so that would become a macro at best. But you can actually get what you want more easily from PROC MEANS + PROC CONTENTS, which will also do all variables at once.

Re: Running the same proc SQL on list of similar variables

Reeza — Wed, 10 Jul 2019 20:38:12 GMT

https://communities.sas.com/t5/SAS-Communities-Library/Library-Datasets-Summary-Macro-DATA-SPECS/ta-p/544757

Maybe this already written macro is useful, just include only the variables you want analyzed?

topic Re: Running the same proc SQL on list of similar variables in New SAS User

Running the same proc SQL on list of similar variables

Re: Running the same proc SQL on list of similar variables

Re: Running the same proc SQL on list of similar variables