topic Re: Create a Label dataset as sample below in SAS Programming

Create a Label dataset as sample below

ybz12003 — Mon, 27 Mar 2023 18:02:32 GMT

Hello,

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;

Re: Create a Label dataset as sample below

PaigeMiller — Mon, 27 Mar 2023 18:10:11 GMT

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???)

Re: Create a Label dataset as sample below

ybz12003 — Mon, 27 Mar 2023 18:31:15 GMT

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(%)"

Re: Create a Label dataset as sample below

MayurJadhav — Mon, 27 Mar 2023 18:48:34 GMT

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.

@ybz12003

%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;

Re: Create a Label dataset as sample below

Tom — Mon, 27 Mar 2023 18:52:08 GMT

Are you just asking how to use the PUT() function?

if not missing(sex) then demo=cats('Sex_',put(sex,sex.));

Re: Create a Label dataset as sample below

PaigeMiller — Mon, 27 Mar 2023 19:54:21 GMT

@ybz12003 wrote:
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.

Re: Create a Label dataset as sample below

MayurJadhav — Mon, 27 Mar 2023 19:23:37 GMT

@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;

Re: Create a Label dataset as sample below

ybz12003 — Mon, 27 Mar 2023 19:42:41 GMT

Thanks for your valuable suggestion. Unfortunately, I got "1-7" numbers at the end of your codes, while Tom's code is more clean to me.