DATA Step, Macro, Functions and more

Extract Information from variable

Accepted Solution Solved
Reply
New Contributor
Posts: 2
Accepted Solution

Extract Information from variable

Hi Guys i have a single variable with all the data in that i need to create multiple variables out of it i have been trying it with substring function but no luck can you please help me out with this thanks

 

what i have:

sample 

name=sammy id=123456 size=213456 type=txt

name=dummy id=34256 size= 2314 type=xlsx

name=sample id=12456 size=1111111111 type=doc

 

what i want in new dataset is

name     id           size              type

sammy  123456  213456           txt

dummy  34256    2314               xlsx

sample  12456     1111111111     doc

 

thanks again

 


Accepted Solutions
Solution
‎06-30-2017 10:09 AM
Super User
Super User
Posts: 7,942

Re: Extract Information from variable

What you have there is a named input file.  You can read it in directly, no need to post process it.  Take a look at:

http://support.sas.com/kb/24/692.html

 

To do it manually is a pain, you would setup a datastep, then loop over the pairs of values (i.e. scan using " " as delimiter) and then scan within that by "=" for first/second pair.  Too much work, do it in the read step.

View solution in original post


All Replies
Solution
‎06-30-2017 10:09 AM
Super User
Super User
Posts: 7,942

Re: Extract Information from variable

What you have there is a named input file.  You can read it in directly, no need to post process it.  Take a look at:

http://support.sas.com/kb/24/692.html

 

To do it manually is a pain, you would setup a datastep, then loop over the pairs of values (i.e. scan using " " as delimiter) and then scan within that by "=" for first/second pair.  Too much work, do it in the read step.

New Contributor
Posts: 2

Re: Extract Information from variable

Hi @RW9

 

the input file that i have is a csv and it doesn't have a variable name over there

I imported it to sas dataset and it just cameup like that

what i have in csv is

name=sammy id=123456 size=213456 type=txt

name=dummy id=34256 size= 2314 type=xlsx

name=sample id=12456 size=1111111111 type=doc

 

what i want in new dataset is

name     id           size              type

sammy  123456  213456           txt

dummy  34256    2314               xlsx

sample  12456     1111111111     doc

 

thanks

venky

 

 

Super User
Super User
Posts: 7,942

Re: Extract Information from variable

It isn't a CSV file you have.  A CSV file means Comma Separated Variable file.  I.e. one row with headers (optional), then one row per data row, with each element separated by a comma.  What you have is a named parameter file which someone has chosen to hange the file extension to CSV.  File extension can be anything you want, and indicate to the OS what program to use to open them.  Excel can open files and process the inform in it as you have CSV associated with Excel, however when Excel reads it, it will not process it properly and show the text all in col1.  To import the file correctly into SAS, ignore Excel, and use the datastep import program example given in the link I provided, this will correctly read the file.  

Then perhaps explain to the person sending the file why giving a file extension the wrong identifier can lead to all kinds of confusion.

Contributor
Posts: 62

Re: Extract Information from variable

data haves;

infile "...\textfile.txt";

input @"name=" name $ @"id=" id @"size=" size @"type=" type $ ;

;run;

Super User
Posts: 11,343

Re: Extract Information from variable


imvenky wrote:

Hi @RW9

 

the input file that i have is a csv and it doesn't have a variable name over there

I imported it to sas dataset and it just cameup like that

what i have in csv is

name=sammy id=123456 size=213456 type=txt

name=dummy id=34256 size= 2314 type=xlsx

name=sample id=12456 size=1111111111 type=doc

  


Please show the code you are using to read that file and maybe post a few lines of the data if possible. If you post some data please use a code boxe opened with the forum {i} menu icon as the main message windows reformat text and may introduce other issues.

PROC Star
Posts: 325

Re: Extract Information from variable

If you have something like this in SAS dataset. You can do use below code. But @RW9 suggestion is the way to go

data newone;
set sample;
name=prxchange('s/name=|id.+//', -1, trim(col1));
id=prxchange('s/^\S+|id=|\s+size.+//',-1 ,trim(col1));
size=prxchange('s/^\S+\s+\S+\s+|size=\s?|\s+.+$//',-1 ,trim(col1));
type=prxchange('s/.+=//',-1 ,trim(col1));
run;
Contributor
Posts: 62

Re: Extract Information from variable

A wonderfull function this "prxchange".

 

Thanks

Regards

Super User
Posts: 10,020

Re: Extract Information from variable

It is called NAMED INPUT method .

 

data have;
input name= $40. id=  size= type= $40.;
cards;
name=sammy id=123456 size=213456 type=txt
name=dummy id=34256 size= 2314 type=xlsx
name=sample id=12456 size=1111111111 type=doc
;
run;
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 8 replies
  • 202 views
  • 3 likes
  • 6 in conversation