BookmarkSubscribeRSS Feed
belboy
Obsidian | Level 7

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 🙂

9 REPLIES 9
PaigeMiller
Diamond | Level 26

@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
belboy
Obsidian | Level 7

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

 

 

Reeza
Super User

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

 

 



belboy
Obsidian | Level 7

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

 

 

PaigeMiller
Diamond | Level 26

@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
belboy
Obsidian | Level 7

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:

 

PaigeMiller
Diamond | Level 26

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

--
Paige Miller
Reeza
Super User

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;

 

 

 

 

belboy
Obsidian | Level 7

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 9 replies
  • 956 views
  • 1 like
  • 3 in conversation