Character variables to columns

[hwangnyc]

Hi everyone, 

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

Services

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

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! 

[hwangnyc]

Hi everyone, 

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

Services

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

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! 

[ballardw]

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

[hwangnyc]

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? 

[Tom]

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.

[hwangnyc]

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?

[SuryaKiran]

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;