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!
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.
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?
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.
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;
Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.