topic Re: Extract Information from variable in SAS Programming

Extract Information from variable

imvenky — Fri, 30 Jun 2017 13:37:18 GMT

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

Re: Extract Information from variable

RW9 — Fri, 30 Jun 2017 13:50:12 GMT

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.

Re: Extract Information from variable

imvenky — Fri, 30 Jun 2017 13:54:53 GMT

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

Re: Extract Information from variable

RW9 — Fri, 30 Jun 2017 14:16:33 GMT

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.

Re: Extract Information from variable

DoumbiaS — Fri, 30 Jun 2017 14:20:18 GMT

data haves;

infile "...\textfile.txt";

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

;run;

Re: Extract Information from variable

ballardw — Fri, 30 Jun 2017 14:45:36 GMT

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

Re: Extract Information from variable

kiranv_ — Fri, 30 Jun 2017 15:26:55 GMT

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;

Re: Extract Information from variable

DoumbiaS — Fri, 30 Jun 2017 16:03:39 GMT

A wonderfull function this "prxchange".

Thanks

Regards

Re: Extract Information from variable

Ksharp — Sat, 01 Jul 2017 06:46:03 GMT

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;