BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
imvenky
Calcite | Level 5

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

 

1 ACCEPTED SOLUTION

Accepted Solutions
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

8 REPLIES 8
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

imvenky
Calcite | Level 5

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

 

 

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

DoumbiaS
Quartz | Level 8

data haves;

infile "...\textfile.txt";

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

;run;

ballardw
Super User

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

kiranv_
Rhodochrosite | Level 12

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;
DoumbiaS
Quartz | Level 8

A wonderfull function this "prxchange".

 

Thanks

Regards

Ksharp
Super User

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;

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
  • 8 replies
  • 1768 views
  • 3 likes
  • 6 in conversation