DATA Step, Macro, Functions and more

Extracting one observation into multiple observations...

Reply
Contributor
Posts: 23

Extracting one observation into multiple observations...

Hi,

Here's my dilemma - I have a dataset that looks like this in SAS 7.1:

USERROLE
abcdeAdmin;Test
fghijkMgr;Test
wxyzSr;Test;DBA

 

I need to convert this data to look like this:

USERROLE
abcdeAdmin
abcdeTest
fghijkMgr
fghijkTest
wxyzSr
wxyzTest
wxyzDBA

 

I tried using a SCAN function but to no avail... any ideas / help would be greatly appreciated.

 

Thanks SAS Community Smiley Happy

Respected Advisor
Posts: 2,796

Re: Extracting one observation into multiple observations...


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.

--
Paige Miller
Contributor
Posts: 23

Re: Extracting one observation into multiple observations...

Posted in reply to PaigeMiller

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

 

USERROLE
abcdeAdmin;Test
fghijkMgr;Test
wxyzSr;Test;DBA

 

NEW

I need to convert the OLD data to look like this:

USERROLE
abcdeAdmin
abcdeTest
fghijkMgr
fghijkTest
wxyzSr
wxyzTest
wxyzDBA

 

 

Super User
Posts: 23,224

Re: Extracting one observation into multiple observations...

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

 

 



Contributor
Posts: 23

Re: Extracting one observation into multiple observations...

Posted in reply to PaigeMiller

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

 

 

Respected Advisor
Posts: 2,796

Re: Extracting one observation into multiple observations...


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?

--
Paige Miller
Contributor
Posts: 23

Re: Extracting one observation into multiple observations...

Posted in reply to PaigeMiller

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:

 

Respected Advisor
Posts: 2,796

Re: Extracting one observation into multiple observations...

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

--
Paige Miller
Super User
Posts: 23,224

Re: Extracting one observation into multiple observations...

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;

 

 

 

 

Contributor
Posts: 23

Re: Extracting one observation into multiple observations...

@Reeza Thank you, thank you Reeza Smiley Happy

 

That is what I needed to get me started.  I didn't know where to start.  

 

thanks for helping Smiley Happy

Ask a Question
Discussion stats
  • 9 replies
  • 115 views
  • 1 like
  • 3 in conversation