I have a Tabulate output dataset as listed below. I created an individual data step as Macro below. I would like to combine three datasets, Age/Sex/Race, the similar result as Want_1 or Want_2 below. Is there a way to do it? Thanks.
proc format; value race 1='White-NH' 2='Black/African American-NH' 3='Non-Hispanic other' 4='Hispanic'; value Age 1='2-4 years' 2='5-11 years' 3='12-17 years'; value Sex 1='Male' 2='Female'; run; data Tabulate_Output; Format Age Age. race race. Sex Sex.; input Age Sex Race N; infile datalines delimiter='/'; datalines; 1/././261/ 2/././283/ 3/././130/ ./1/./799/ ./2/./117/ ././1/220/ ././2/111/ ././3/55/ ; run; %Macro A (Var); data &Var.; set Tabulate_Output; if &Var. ^=.; keep &Var. N; run; %mend; %A (Age); %A (Sex); %A (Race); data Want_1; input N Demo $; infile datalines delimiter='/'; datalines; 261/Age_1 283/Age_2 130/Age_3 799/Sex_1 117/Sex_2 220/Race_1 111/Race_2 55/Race_3 ; run; data Want_2; input N Demo $; infile datalines delimiter='/'; datalines; 261/Age_2-4y 283/Age_5-11y 130/Age_12-17y 799/Sex_M 117/Sex_F 220/Race_White 111/Race_Black 55/Race_Other ; run;
Hello, @ybz12003 please take this as constructive criticism ... this strikes me as something an experienced programmer would avoid doing, but less experienced programmers want to do. I see little value here, and a PROC PRINT of data set Tabulate_Output would achieve the same display of the data. So, I recommend you don't do this without a VERY good reason. (Okay, why do you want to do this???)
I would like to create a certain format Table. N column is not the final variable, it actually a N+PCN, and format is like "N(%)"
I don't see how the original question moves you in that direction. Creating character strings that have the variable name and numeric values in the character string still seems like a poor idea (which makes me cringe) that doesn't get you anywhere. Data is easier to work with when text and numeric values and variable names are separate. And so again I urge you to not do this, and re-think the process. If you could explain the big picture of what you are trying to do, rather than explain this particular step, I'm sure some of us could help you re-design the process in a more effective way.
Please look at the %TABLEN macro.
To combine datasets you first need same variable names which is not the case here. You can rename columns name from age to demo, sex to demo, and race to demo. But problem with this approach is they've formatted values. When you attempt to combine those datasets you will not get desired results due to different sas formats.
You can explicitly create DEMO variable by passing values in your sas macro and then combine datasets. Here is the code that might help you. Though I haven't understood your requirement fully but you can play with this code to produce desired output.
%Macro A (Var); data &Var.; length N 8. Demo $50. ; set Tabulate_Output; if &Var. ^=.; Demo = put(&Var.,&Var..); keep N Demo; run; %mend; %A (Age); %A (Sex); %A (Race); data Want; set Age Sex Race; run;
@ybz12003 Here you can adjust PUT() function according to your VAR. to assign value to DEMO variable as per your requirement. I see in your Want datasets, there is a sequence attached after underscore(_), it can be attached using "_N_" var.
Adjust %IF ELSE statements accordingly but here is the code just for your reference:
%if "&Var."="Age" %then %do; Demo=cat('Age_', put(&Var., &Var..), _N_); %end; %else %if "&Var."="Sex" %then %do; Demo=cat('Sex_', put(&Var., &Var..), _N_); %end; %else %if "&Var."="Race" %then %do; Demo=cat('Race_', put(&Var., &Var..), _N_); %end;
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.