topic Re: Character variables to columns in SAS Programming

Character variables to columns

hwangnyc — Fri, 01 Feb 2019 14:41:07 GMT

Hi everyone,

I have an excel file that has data that looks like this:

Services

BC-1

BC-1
MI-1

BC-1
PC-1
MI-1

Assess B-1
BC-1
HR-1
MHA-1

CC-1

Assess B-1
BC-1

HR-4

BN-1

What I would like to do is transpose each category into a column from each cell. So what I would like is:

BC	MI	PC	AssessB	HR	BN	MHA	CC
1	0	0	0	0	0	0	0
0	0	0	0	0	0	0	0
0	0	0	0	0	0	0	0
1	1	0	0	0	0	0	0
0	0	0	0	0	0	0	0
1	1	1	0	0	0	0	0
1	0	0	1	1	0	1	0
0	0	0	0	0	0	0	1
1	0	0	1	0	0	0	0
0	0	0	0	4	0	0	0

Any help would be greatly appreciated thank you!

Re: Character variables to columns

ballardw — Fri, 01 Feb 2019 15:08:37 GMT

So what does the SAS data set look like made from that Excel data?

Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-data-AKA-generate/ta-p/258712 will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the {i} icon or attached as text to show exactly what you have and that we can test code against.

Re: Character variables to columns

SuryaKiran — Fri, 01 Feb 2019 15:10:51 GMT

Import the excel file using PROC IMPORT, then you can write a data step to parse your data using some functions in sas.

proc import 
  datafile="/user/kiran/Services.xlsx" 
  dbms=xlsx
  out=work.have 
  replace;
run;

/* For my test, I have the datastep instead of import */
data have;
infile datalines truncover;
input Services $100.;
datalines;
BC-1
0
0
BC-1 MI-1
0
BC-1 PC-1 MI-1
Assess B-1 BC-1 HR-1 MHA-1
CC-1
Assess B-1 BC-1
HR-4
BN-1
;
run;

data want;
set have;
BC=ifc(find(Services,'BC-')>0,substr(Services,find(Services,'BC-')+3,1),'0');
MI=ifc(find(Services,'MI-')>0,substr(Services,find(Services,'MI-')+3,1),'0');
PC=ifc(find(Services,'PC-')>0,substr(Services,find(Services,'PC-')+3,1),'0');
AssessB=ifc(find(Services,'Assess B-')>0,substr(Services,find(Services,'Assess B-')+9,1),'0');
HR=ifc(find(Services,'HR-')>0,substr(Services,find(Services,'HR-')+3,1),'0');
BN=ifc(find(Services,'BN-')>0,substr(Services,find(Services,'BN-')+3,1),'0');
MHA=ifc(find(Services,'MHA-')>0,substr(Services,find(Services,'MHA-')+4,1),'0');
CC=ifc(find(Services,'CC-')>0,substr(Services,find(Services,'CC-')+3,1),'0');
run;

Re: Character variables to columns

hwangnyc — Fri, 01 Feb 2019 16:12:51 GMT

Hello!

When I imported the excel file - SAS only took the first few characters, cutting off all the data underneath.

So data that is in Excel like this:

OP - 1
ALC - 1

Became this:

OP-1

in as SAS Data set.

Thoughts?

Re: Character variables to columns

Tom — Fri, 01 Feb 2019 16:52:15 GMT

Are you saying that in Excel the single cells displays as multiple lines? If so it probably has a LineFeed character embedded in the string.

You can use the $HEX format to display some of the values in your SAS dataset to see what character is inserted between the two values. '0A'x is a linefeed and '0D'x is a carriage return. If you try to print those without using the $HEX format then they will cause the location to move to the beginning of the line (carriage return) or jump down one row (line feed) which can make a print out look very confusing.

Do you have an XLS (old format) or XLSX (new format) file? Or some other format.

What method did you use to convert it to a SAS dataset? PROC IMPORT? If so what DBMS setting did you use? XLSX? EXCEL? Let SAS guess based on the file extension?

If you used XLSX engine then it should make the variable long enough for the longest string in the column.

Re: Character variables to columns

hwangnyc — Fri, 01 Feb 2019 18:22:40 GMT

Hi Tom, You're spot on! it imported on a seperate line in SAS as well. It was just hidden when I glanced at the dataset.

Is there a way I can parse out the data by lines?