Rearrange Variables in the same Column

[luvscandy27]

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. 

[AhmedAl_Attar]

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

[luvscandy27]

My results were FMTSEARCH=(WORK LIBRARY)

[AhmedAl_Attar]

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

[luvscandy27]

@AhmedAl_Attar @A_Kh Thank you both for all of your help. I was able to get the code to work 

perfectly. As it turns out, I misspelled characteristic which was why I received the error 🙂

[Reeza]

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 🙂