topic Re: Rearrange Variables in the same Column in SAS Programming

Rearrange Variables in the same Column

luvscandy27 — Tue, 17 Oct 2023 12:28:31 GMT

Hi All,

I am in need of your assistance please. I have data like this:

Chararacteristic
White only:
Below poverty
At or above poverty
Black or African American only:
Below poverty
At or above poverty
Hispanic or Latino:
Below poverty
At or above poverty

But I am trying to get my data like below. Any suggestions would be greatly appreciated:

White only:	Below Poverty
White only:	At or above poverty
Black or African American only:	Below Poverty
Black or African American only:	At or above poverty
Hispanic or Latino:	Below Poverty
Hispanic or Latino:	At or above poverty

Re: Rearrange Variables in the same Column

A_Kh — Tue, 17 Oct 2023 13:15:46 GMT

One way to do so is creating two temporary variables and mapping values from the source variable there using RETAIN;

eg;

data have;
infile cards dlm=',' truncover;
length Chararacteristic $100;
input Chararacteristic;
cards; 
White only:,	 
Below poverty,	 
At or above poverty,	 
Black or African American only:,	 
    Below poverty,	 
    At or above poverty,	 
Hispanic or Latino:, 	 
   Below poverty,	 
  At or above poverty
;
proc print; run; 

data want;
	set have;
	length temp1 temp2 $100;
	retain temp1;
	if find(Chararacteristic, ':') gt 0 then temp1= Chararacteristic;
	else temp2=Chararacteristic;
	if temp2 ne '';
	drop Chararacteristic;
proc print;run;

Re: Rearrange Variables in the same Column

PaigeMiller — Tue, 17 Oct 2023 14:04:33 GMT

I'm sure the solution from @A_Kh works, but question for @luvscandy27: are you 100% sure that there will always be a colon at the end of the text that you want to stay in column 1 and never a colon in the text you want to move to column 2?

Re: Rearrange Variables in the same Column

luvscandy27 — Tue, 17 Oct 2023 14:03:28 GMT

There will not always be a colon at the end. This is just a portion of the dataset. The dataset has several situations

like the one I posted. I was hoping if i got a solution, I would be able to modify the code and apply the code to similar situations

in the dataset.

Re: Rearrange Variables in the same Column

PaigeMiller — Tue, 17 Oct 2023 14:05:50 GMT

Please explain the structure of the data to us, so we can help provide a solution. What is the proper indication in the text that indicates text that is to stay in column 1, or that indicates text that should be moved to column 2? Please provide sample data that is more realistic to your actual problem.

Re: Rearrange Variables in the same Column

luvscandy27 — Tue, 17 Oct 2023 16:30:36 GMT

Below please see the a sample of the table:

Source in PDF: https://www.cdc.gov/nchs/data/hus/2020-2021/VaxCh.pdf

Source in Excel: https://ftp.cdc.gov/pub/Health_Statistics/NCHS/Publications/Health_US/hus20-21tables/VaxCh.xlsx

There is not an identification variable which complicates thing even more.

Re: Rearrange Variables in the same Column

luvscandy27 — Tue, 17 Oct 2023 14:17:02 GMT

The complete table is segmented by types of vaccinations:

Combined 7-vaccine series

DT\PD\TDTap (4 doses or more)

Hepatitis A (2 doses or more)

Hepatitis B (3 doses or more)

Hib (full series)

Measles, mumps, rubella (1 dose or more)

PCV (4 doses or more)

Polio (3 doses or more)

Rotavirus vaccine

Varicella (1 dose or more)

Re: Rearrange Variables in the same Column

PaigeMiller — Tue, 17 Oct 2023 14:18:51 GMT

Perhaps you can come up with some rules, in words, that identify what text remains in column 1 and what text should move to column 2 (and is there a column 3 here?)

Also, I think we would need to see how this full table gets re-arranged, including the birth year columns.

The more you can tell us about what you want, the better chances we will have to create such code.

Re: Rearrange Variables in the same Column

Tom — Tue, 17 Oct 2023 14:19:31 GMT

That looks like a report. Do you have it as a text file you are reading in?

Do you really have it as a SAS dataset?

Or do you have some other dataset that you used to produce that report? If so what does that dataset look like. What code did you use to produce that report?

Re: Rearrange Variables in the same Column

PaigeMiller — Tue, 17 Oct 2023 14:19:41 GMT

@luvscandy27 wrote:

The complete table is segmented by types of vaccinations:

Combined 7-vaccine series

DT\PD\TDTap (4 doses or more)

Hepatitis A (2 doses or more)

Hepatitis B (3 doses or more)

Hib (full series)

Measles, mumps, rubella (1 dose or more)

PCV (4 doses or more)

Polio (3 doses or more)

Rotavirus vaccine

Varicella (1 dose or more)

I don't see how this relates to the problem you asked about. Please explain this in more detail.

Re: Rearrange Variables in the same Column

AhmedAl_Attar — Tue, 17 Oct 2023 14:45:54 GMT

Hi @luvscandy27

Stealing the part of the sample code from @A_Kh, here is one way how you can get what you looking for

/* Create sample data */
data have;
infile cards dlm=',' truncover;
length Chararacteristic $100;
input Chararacteristic;
cards; 
White only:,	 
Below poverty,	 
At or above poverty,	 
Black or African American only:,	 
    Below poverty,	 
    At or above poverty,	 
Hispanic or Latino:, 	 
   Below poverty,	 
  At or above poverty
;
run;

/* Create a Format that covers all sub-categories of interest regardless of their main characteristics */
proc formats lib=work;
	value $subs
	'Below poverty' = '*' 
	'At or above poverty' = '*'
	;
run;

data want (DROP=Chararacteristic);
	length main_char $40 sub_char $20;
	retain main_char;
	set have;
	if (PUT(Chararacteristic,$subs.)='*') then sub_char = Chararacteristic;
	else main_char = Chararacteristic;
	if sub_char ne ''; /* Remove header row (Main_char only) */
run;

Hope this helps

Re: Rearrange Variables in the same Column

luvscandy27 — Tue, 17 Oct 2023 15:41:17 GMT

Hi,

Thank you. I just have one issue. When I run the code in SAS Studio it runs fine. However, when I

run the code in SAS 9.4 I receive an error on this part of code:

if (PUT(Chararacteristic,$subs.)='*') then sub_char = Chararacteristic;

that the Error 48-59: The format subs was not found or could not be loaded. The format was created successfully

in the previous step.

Re: Rearrange Variables in the same Column

AhmedAl_Attar — Tue, 17 Oct 2023 15:47:01 GMT

Check the fmtsearch option in your SAS 9.4 Display Manager.
Proc options option=fmtsearch; run;
if WORK is not part of it, then add WORK library to the option settings

Re: Rearrange Variables in the same Column

luvscandy27 — Tue, 17 Oct 2023 16:13:14 GMT

My results were FMTSEARCH=(WORK LIBRARY)

Re: Rearrange Variables in the same Column

Reeza — Tue, 17 Oct 2023 16:18:05 GMT

Ugly...but I have done this lol.

Save it as a CSV file.

Read the file in using a data step:

Use FIRSTOBS to control the first row of data read in.
Make sure to play around with the informats to ensure you are reading in the first variable with all the spaces correctly counted.
Create a variable that counts the number of spaces in front of the text
Use that to create a level variable. That will tell you how many new variables you need to create as header variables. Looking closely at your data you have subtotals and totals all broken down so you need to account for that as well.
Once you have the levels sorted out, you can use RETAIN to keep the variable header counts in the right locations
Add another column to indicate if a row is an aggregation/subtotal row as well.

Census bureaus have a format for these type of files that are shared via CSV and this is roughly how they do it.

Or...manually clean up the file, since it's likely a one time read 🙂

Re: Rearrange Variables in the same Column

Reeza — Tue, 17 Oct 2023 16:21:35 GMT

FYI - your name is in screenshot - may want to remove in future.

Re: Rearrange Variables in the same Column

luvscandy27 — Tue, 17 Oct 2023 16:25:55 GMT

Thank you. Do you know of anyway I can remove?

Re: Rearrange Variables in the same Column

Reeza — Tue, 17 Oct 2023 16:38:14 GMT

I removed it for you - just take slightly different screenshots in the future. Honestly, for this, I would just clean up the file manually and the excel is messy so you'd probably need to reference the PDF which is formatted correctly anyways 😞

PS. please double check I have the links correctly and the right file attached. If not, you can click the side of the post and edit it as needed.

Re: Rearrange Variables in the same Column

AhmedAl_Attar — Tue, 17 Oct 2023 17:46:02 GMT

@luvscandy27

Hmm!, In this case I would double check the formats catalog in the WORK library, to ensure the $subs. format is created correctly.
You might as well test it on a different data set.

Re: Rearrange Variables in the same Column

luvscandy27 — Tue, 17 Oct 2023 17:50:34 GMT

Thank you!