topic Re: Extracting one observation into multiple observations... in SAS Programming

Extracting one observation into multiple observations...

belboy — Thu, 01 Mar 2018 16:40:54 GMT

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 🙂

Re: Extracting one observation into multiple observations...

PaigeMiller — Thu, 01 Mar 2018 16:43:18 GMT

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

Re: Extracting one observation into multiple observations...

belboy — Thu, 01 Mar 2018 16:53:05 GMT

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

Re: Extracting one observation into multiple observations...

Reeza — Thu, 01 Mar 2018 17:11:34 GMT

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

Re: Extracting one observation into multiple observations...

belboy — Thu, 01 Mar 2018 17:11:39 GMT

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

Re: Extracting one observation into multiple observations...

PaigeMiller — Thu, 01 Mar 2018 17:14:01 GMT

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

Re: Extracting one observation into multiple observations...

belboy — Thu, 01 Mar 2018 17:25:10 GMT

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:

Re: Extracting one observation into multiple observations...

PaigeMiller — Thu, 01 Mar 2018 17:29:02 GMT

I want you to show us the code your tried with the SCAN function. The entire data step with the SCAN function.

Re: Extracting one observation into multiple observations...

Reeza — Thu, 01 Mar 2018 17:35:46 GMT

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;

Re: Extracting one observation into multiple observations...

belboy — Thu, 01 Mar 2018 17:48:05 GMT

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