Hi,
Here's my dilemma - I have a dataset that looks like this in SAS 7.1:
USER | ROLE |
abcde | Admin;Test |
fghijk | Mgr;Test |
wxyz | Sr;Test;DBA |
I need to convert this data to look like this:
USER | ROLE |
abcde | Admin |
abcde | Test |
fghijk | Mgr |
fghijk | Test |
wxyz | Sr |
wxyz | Test |
wxyz | DBA |
I tried using a SCAN function but to no avail... any ideas / help would be greatly appreciated.
Thanks SAS Community 🙂
@belboy wrote:
I tried using a SCAN function but to no avail... any ideas / help would be greatly appreciated.
This is a meaningless statement to us. Show us your code and show us the SASLOG, so we can see what you might have done wrong.
The dataset already exists in my organization as I didn't create the file myself. It looks like some basic flat file imported from Excel.
However, the real question is what code / function would I write to change the output of this old dataset into a new dataset:
OLD
USER | ROLE |
abcde | Admin;Test |
fghijk | Mgr;Test |
wxyz | Sr;Test;DBA |
NEW
I need to convert the OLD data to look like this:
USER | ROLE |
abcde | Admin |
abcde | Test |
fghijk | Mgr |
fghijk | Test |
wxyz | Sr |
wxyz | Test |
wxyz | DBA |
SCAN is the correct approach.
If your code didn't work, show us what you tried so we can show you where you made a mistake.
@belboy wrote:
The dataset already exists in my organization as I didn't create the file myself. It looks like some basic flat file imported from Excel.
However, the real question is what code / function would I write to change the output of this old dataset into a new dataset:
OLD
USER ROLE abcde Admin;Test fghijk Mgr;Test wxyz Sr;Test;DBA
NEW
I need to convert the OLD data to look like this:
USER ROLE abcde Admin abcde Test fghijk Mgr fghijk Test wxyz Sr wxyz Test wxyz DBA
@PaigeMiller Is there no way to extract one observation into multiple obs?
I will try to get the import code for this if necessary.... but thought that someone in the community might have a simple solution (i.e., Role=Scan(1,"';") ) I don't know... I'm drawing a blank.
I thought of maybe:
where Role ? ";" then output;
and perhaps infile / input the data back in correctly? Just curious if anyone has a more efficient manner....
@belboy wrote:
@PaigeMiller Is there no way to extract one observation into multiple obs?
Of course there are ways to do this.
I asked to see your code and your SASLOG because you are probably making a simple mistake that is easily fixed. Can you show us your code and SASLOG?
Gotcha - well here is the PROC Import code for it.
DATA WORK.ABKSSYS;
LENGTH
F1 $ 8
F2 $ 21
F3 $ 10
F4 8 ;
FORMAT
F1 $CHAR8.
F2 $CHAR21.
F3 $CHAR10.
F4 BEST32. ;
INFORMAT
F1 $CHAR8.
F2 $CHAR21.
F3 $CHAR10.
F4 BEST32. ;
INFILE 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxx'
LRECL=83
ENCODING="LATIN1"
TERMSTR=CRLF
DLM='7F'x
MISSOVER
DSD ;
INPUT
F1 : $CHAR8.
F2 : $CHAR21.
F3 : $CHAR10.
F4 : ?? BEST32. ;
RUN;
As I said, it's an existing file and I didn't create it...so there is no code for it. I just need the end result to be as I previously mentioned:
I want you to show us the code your tried with the SCAN function. The entire data step with the SCAN function.
We wanted to see your SCAN() attempt not import code.
Either way,
Use COUNTW() to see the number of items in the list.
Use a DO loop
use SCAN()
Untested demo code:
x = countw(variable);
do i=1 to x;
word =scan(variable, i, ";");
output;
end;
@Reeza Thank you, thank you Reeza 🙂
That is what I needed to get me started. I didn't know where to start.
thanks for helping 🙂
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.