topic Re: Assistance in reformatting multicategorical variable. in SAS Programming

Assistance in reformatting multicategorical variable.

aigiss — Wed, 15 Feb 2023 20:35:37 GMT

Hello everyone. I have a dataset called combined_dataset. I have a variable called "genderid" with multiple subcategorical variables, see below:

I want to conduct a 2x2 table with each of these subcategorical variables with my disease outcome of interest. However, I need to make each categories into a binary variable. I tried doing female by doing this code:

proc format; value $female "female" = "yes" other = "no"; run; data combined_dataset; set k.combined_dataset; if genderid = "Female" then Female2= "Yes"; else Female2= "No"; run;

However I looked over the frequency table for that specific variable, I get this result:

What am I doing wrong? I appreciate all the help!

Re: Assistance in reformatting multicategorical variable.

ballardw — Wed, 15 Feb 2023 20:51:41 GMT

If you want to use a format make sure the actual values of the variable are used. Your Proc freq output shows "Female" but your format wants to use "female". You also do not use the format $female anywhere so I am not sure why you include that.

I would expect this to yield what I think you want.

proc format;
  value $female
    "Female" = "Yes"
    other = "No";
run;

proc freq data=k.combined_dataset;
   tables genderid;
   format genderid $female. ;
run;

Please show the LOG for the Proc Freq that generated the "incorrect" output. If needed re-run the code. Then go to the log, copy from the log the code and all the notes or messages and then on the forum open a text box with the </> icon and paste the text.

You may have to re-run the data step showing us the log there as well. I might expect that shown incorrect output if a prior data step had run as and not actually replaced with code that used the "else Female2="No"; "

data combined_dataset;
  set k.combined_dataset;
  if genderid = "Female" then Female2= "Yes";
run;

Re: Assistance in reformatting multicategorical variable.

aigiss — Wed, 15 Feb 2023 21:14:00 GMT

This is the result of the last code:

The log shows no issue of the proposed code. However, the issue is that I cannot conduct other multivariables I need to reformat. This is not what I am looking for.

Re: Assistance in reformatting multicategorical variable.

ballardw — Wed, 15 Feb 2023 21:46:37 GMT

@aigiss wrote:

This is the result of the last code:

The log shows no issue of the proposed code. However, the issue is that I cannot conduct other multivariables I need to reformat. This is not what I am looking for.

Then show us what your are looking for. Please do not make us guess. Your only example shows one format and one variable with two values. So we have no idea what you may want.

Re: Assistance in reformatting multicategorical variable.

Reeza — Wed, 15 Feb 2023 22:12:43 GMT

I would instead recommend dummy coding your categorical variable.
https://blogs.sas.com/content/iml/2020/08/31/best-generate-dummy-variables-sas.html

Re: Assistance in reformatting multicategorical variable.

aigiss — Wed, 15 Feb 2023 22:34:33 GMT

Hi there I did not mean to make you guess. I assumed the description above is sufficient to know what I wanted. But here is what I want.

In the genderid there are multiple subcategories

Genderid =

Female

Male

Unknown/Missing

Transgender Male

etc...

What want to do is breakdown each subcategories to have a binary result:

Female Y/N

Male Y/N

Unknown Y/N

Please let me know if you need more clarifications.

Re: Assistance in reformatting multicategorical variable.

Reeza — Wed, 15 Feb 2023 22:38:30 GMT

This generates a 2x1 table in PROC FREQ not a 2x2 table. If you want a 2x2 table some other variable must be identified.

Re: Assistance in reformatting multicategorical variable.

aigiss — Wed, 15 Feb 2023 22:45:48 GMT

Correct. And to do so I would like to create a new variable column that has females with Y/N (observations are string) under the genderid variable.

Re: Assistance in reformatting multicategorical variable.

Reeza — Wed, 15 Feb 2023 23:09:37 GMT

option validvarname=v7;
proc format;
value yesno 
1='Yes' 0='No';
run;

proc glmselect data=sashelp.class NOPRINT outdesign(addinputvars)=Want;
   class      sex;   /* list the categorical variables here */
   model weight = sex /  noint selection=none;
run;

proc freq data=want;
table sex_f sex_m;
format sex_: yesno.;
run;

You can run this to see how the dummy variables in the example I linked to previously make this trivial to do.

Re: Assistance in reformatting multicategorical variable.

ballardw — Wed, 15 Feb 2023 23:12:21 GMT

@aigiss wrote:

Hi there I did not mean to make you guess. I assumed the description above is sufficient to know what I wanted. But here is what I want.

In the genderid there are multiple subcategories

Genderid =

Female

Male

Unknown/Missing

Transgender Male

etc...

What want to do is breakdown each subcategories to have a binary result:

Female Y/N

Male Y/N

Unknown Y/N

Please let me know if you need more clarifications.

Mud. Clear as.

Provide some data with values of the genderid as data step code or at least plain text pasted into a text box, not a picture (cannot code against pictures) and then show a manually created report /count of what you are expecting from that example data. Make sure the counts are different numbers so we can trace some logic.