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

Please help, since want to call this data in a table using cards/dataline option.
There are Total 5 variable and please note name starts with Mr/mrs.

Header are: Ref_no  Mode Plan   Name   Amount

Sharing third row as ex :

Ref_no             Mode           Plan                                                    Name                            Amount

Y10800539      YEARLY      MARS Family REVENUE Builder      Mrs. sina Manahattan      970

Observations are :


Y10800542 YEARLY MARS PLAN Vriddhi Mr. Surendra gita Kumar 100.4
Y10800543 YEARLY MARS PLAN Vriddhi Mr. Rahul kapu  Man 969.83
Y10800539 YEARLY MARS Family REVENUE Builder Mrs. sina Manahattan 970
Y10800539 YEARLY MARS LifeLONG BEST Mr Virag Bashmukh 86
Y10800539 YEARLY MARS LifeLONG BEST Mr. Tricky behoshi H 94.5
Y10800539 YEARLY MARS Young Scholar Secure Mr. Joyton Flavian sequence 500
Y10800543 YEARLY MARS Family REVENUE Builder Mr. Suraj putraJoban 5000
Y10800543 YEARLY MARS PLAN Varsha Ms. Mahalakshmi krishnanram 504
Y10800543 YEARLY MARS PLAN Vriddhi Mr. Himanshu Pahwa 3003.75
Y10800543 YEARLY MARS PLAN Vriddhi Mr. Sanjay Vaarma 213.54
Y10800543 YEARLY MARS PLAN Vriddhi Mrs. Jane Saggarwal 3327
Y10800543 YEARLY MARS LIVE SMART Mr Syed husain Jasmeet 800
Y10800543 YEARLY MARS PLAN Vriddhi Mr Rajesh babr 1000.69
Y10800543 YEARLY MARS PLAN Vriddhi Mr. Rajan visal 241.53


rgds
sunil

1 ACCEPTED SOLUTION

Accepted Solutions
Linlin
Lapis Lazuli | Level 10

data out;

infile cards;

input;

keepit = _infile_;

cards;

Y10800542 YEARLY MARS PLAN Vriddhi Mr. Surendra gita Kumar 100.4

Y10800543 YEARLY MARS PLAN Vriddhi Mr. Rahul kapu  Man 969.83

Y10800539 YEARLY MARS Family REVENUE Builder Mrs. sina Manahattan 970

Y10800539 YEARLY MARS LifeLONG BEST Mr Virag Bashmukh 86

Y10800539 YEARLY MARS LifeLONG BEST Mr. Tricky behoshi H 94.5

Y10800539 YEARLY MARS Young Scholar Secure Mr. Joyton Flavian sequence 500

Y10800543 YEARLY MARS Family REVENUE Builder Mr. Suraj putraJoban 5000

Y10800543 YEARLY MARS PLAN Varsha Ms. Mahalakshmi krishnanram 504

Y10800543 YEARLY MARS PLAN Vriddhi Mr. Himanshu Pahwa 3003.75

Y10800543 YEARLY MARS PLAN Vriddhi Mr. Sanjay Vaarma 213.54

Y10800543 YEARLY MARS PLAN Vriddhi Mrs. Jane Saggarwal 3327

Y10800543 YEARLY MARS LIVE SMART Mr Syed husain Jasmeet 800

Y10800543 YEARLY MARS PLAN Vriddhi Mr Rajesh babr 1000.69

Y10800543 YEARLY MARS PLAN Vriddhi Mr. Rajan visal 241.53

;

data want;

  set out;

ref_no=scan(keepit,1,' ');

mode=scan(keepit,2,' ');

call scan(keepit,-1,p1,l1,' ');

call scan(keepit,3,p2,l2,' ');

n=max(find(keepit,'Mr'),find(keepit,'Ms'),find(keepit,'Mrs'));

amont=scan(keepit,-1,' ');

name=substr(keepit,n,p1-n);

plan=substr(keepit,p2,n-p2);

drop p1 p2 l1 l2 n keepit;

proc print;run;

View solution in original post

11 REPLIES 11
AncaTilea
Pyrite | Level 9

Hi.

This is a nice problem.

I unfortunately only were able to solve the simple part of it, but by replying now, it will bring it to the attention of those that will find the elegant solution in a blink:

DATA WANT;

INPUT STRING $80.;

    ref_no = scan(string,1, "");

    mode = scan( string, 2, "" );

    price = scan( string, -1, "" );

name = compbl(prxchange("s/.*MARS|PLAN|Family|Revenue|LifeLong|Vriddhi|BEST|Live|Smart|Builder|Young Scholar|Secure|[0-9]|Varsha/ /i", -1,string));
DATALINES;

Y10800539 YEARLY MARS Family REVENUE Builder Mrs. sina Manahattan      970

Y10800542 YEARLY MARS PLAN Vriddhi Mr. Surendra gita Kumar 100.4

Y10800543 YEARLY MARS PLAN Vriddhi Mr. Rahul kapu  Man 969.83

Y10800539 YEARLY MARS Family REVENUE Builder Mrs. sina Manahattan 970

Y10800539 YEARLY MARS LifeLONG BEST Mr Virag Bashmukh 86

Y10800539 YEARLY MARS LifeLONG BEST Mr. Tricky behoshi H 94.5

Y10800539 YEARLY MARS Young Scholar Secure Mr. Joyton Flavian sequence 500

Y10800543 YEARLY MARS Family REVENUE Builder Mr. Suraj putraJoban 5000

Y10800543 YEARLY MARS PLAN Varsha Ms. Mahalakshmi krishnanram 504

Y10800543 YEARLY MARS PLAN Vriddhi Mr. Himanshu Pahwa 3003.75

Y10800543 YEARLY MARS PLAN Vriddhi Mr. Sanjay Vaarma 213.54

Y10800543 YEARLY MARS PLAN Vriddhi Mrs. Jane Saggarwal 3327

Y10800543 YEARLY MARS LIVE SMART Mr Syed husain Jasmeet 800

Y10800543 YEARLY MARS PLAN Vriddhi Mr Rajesh babr 1000.69

Y10800543 YEARLY MARS PLAN Vriddhi Mr. Rajan visal 241.53

;

Basically you will have to use some character function (scan, substr, findw...) to parse the string...I am still working on it. Got the name down.

Cheers!

Anca.

sunilzood
Calcite | Level 5

Anca, Good try!!! u were close. thanks!!!

Linlin
Lapis Lazuli | Level 10

data out;

infile cards;

input;

keepit = _infile_;

cards;

Y10800542 YEARLY MARS PLAN Vriddhi Mr. Surendra gita Kumar 100.4

Y10800543 YEARLY MARS PLAN Vriddhi Mr. Rahul kapu  Man 969.83

Y10800539 YEARLY MARS Family REVENUE Builder Mrs. sina Manahattan 970

Y10800539 YEARLY MARS LifeLONG BEST Mr Virag Bashmukh 86

Y10800539 YEARLY MARS LifeLONG BEST Mr. Tricky behoshi H 94.5

Y10800539 YEARLY MARS Young Scholar Secure Mr. Joyton Flavian sequence 500

Y10800543 YEARLY MARS Family REVENUE Builder Mr. Suraj putraJoban 5000

Y10800543 YEARLY MARS PLAN Varsha Ms. Mahalakshmi krishnanram 504

Y10800543 YEARLY MARS PLAN Vriddhi Mr. Himanshu Pahwa 3003.75

Y10800543 YEARLY MARS PLAN Vriddhi Mr. Sanjay Vaarma 213.54

Y10800543 YEARLY MARS PLAN Vriddhi Mrs. Jane Saggarwal 3327

Y10800543 YEARLY MARS LIVE SMART Mr Syed husain Jasmeet 800

Y10800543 YEARLY MARS PLAN Vriddhi Mr Rajesh babr 1000.69

Y10800543 YEARLY MARS PLAN Vriddhi Mr. Rajan visal 241.53

;

data want;

  set out;

ref_no=scan(keepit,1,' ');

mode=scan(keepit,2,' ');

call scan(keepit,-1,p1,l1,' ');

call scan(keepit,3,p2,l2,' ');

n=max(find(keepit,'Mr'),find(keepit,'Ms'),find(keepit,'Mrs'));

amont=scan(keepit,-1,' ');

name=substr(keepit,n,p1-n);

plan=substr(keepit,p2,n-p2);

drop p1 p2 l1 l2 n keepit;

proc print;run;

sunilzood
Calcite | Level 5

Hi Linlin, Thanks its working fine.

But request to please elaborate the below highlights- what is happening with p1,l1p2,l2,-n,

call scan(keepit,-1,p1,l1,' ');

call scan(keepit,3,p2,l2,' ');

n=max(find(keepit,'Mr'),find(keepit,'Ms'),find(keepit,'Mrs'));

name=substr(keepit,n,p1-n);

plan=substr(keepit,p2,n-p2);

sunil

umashankersaini
Quartz | Level 8

Hi,

I am trying to explain..

Call scan : Returns the position and length of the nth word from a character string.

Syntax  : call scan(keepit,-1,p1,l1,' ');

keepit :- String, in which we need to find out the position and length.

-1 :- Count , If count is positive, then CALL SCAN counts words from left to right in the character string, if negative then from right to left. (same as for scan function)

P1 and L1 are the nemeric variables

p1 :-  Position , specifies a numeric variable in which the position of the word is returned.

L1 :-  Length , specifies a numeric variable in which the length of the word is returned.

' '  :-  delimiter (same as for scan function)

Syntax :  n=max(find(keepit,'Mr'),find(keepit,'Ms'),find(keepit,'Mrs'));

to find the position number of 'Mr' , 'Ms' and 'Mrs'.

for more information related to call scan : http://support.sas.com/documentation/cdl/en/lrdict/64316/HTML/default/viewer.htm#a002255934.htm

Linlin ----- Kindly correct me, if i am worng somewhere. looking for your valuable input.

Regards

Uma Shanker Saini


sunilzood
Calcite | Level 5

Understood!!! Thanks Dude.

data_null__
Jade | Level 19

The "key" to reading this file is the title on the name field.  It acts as a special field delimiter of sorts.

data out;
   infile cards column=c stopoVer length=len;
   input refno:$9. mode:$10. @;
   c1=c;
  
input @(sum(findw(_infile_,'Mr','. '),findw(_infile_,'Mrs',' .'),findw(_infile_,'Ms',' .'))) @;
   c2=c;
   l1=c2-c1;
  
input @c1 plan $Varying64. l1 @(find(strip(_infile_),' ',-len)) @;
   c3=c;
   l2=c3-c2+
1;
  
input @c2 name $Varying64. l2 amount;
  
drop c: l:;
   cards;
Y10800542 YEARLY MARS PLAN Vriddhi Mr. Surendra gita Kumar 100.4
Y10800543 YEARLY MARS PLAN Vriddhi Mr. Rahul kapu  Man 969.83
Y10800539 YEARLY MARS Family REVENUE Builder Mrs. sina Manahattan 970
Y10800539 YEARLY MARS LifeLONG BEST Mr Virag Bashmukh 86
Y10800539 YEARLY MARS LifeLONG BEST Mr. Tricky behoshi H 94.5
Y10800539 YEARLY MARS Young Scholar Secure Mr. Joyton Flavian sequence 500
Y10800543 YEARLY MARS Family REVENUE Builder Mr. Suraj putraJoban 5000
Y10800543 YEARLY MARS PLAN Varsha Ms. Mahalakshmi krishnanram 504
Y10800543 YEARLY MARS PLAN Vriddhi Mr. Himanshu Pahwa 3003.75
Y10800543 YEARLY MARS PLAN Vriddhi Mr. Sanjay Vaarma 213.54
Y10800543 YEARLY MARS PLAN Vriddhi Mrs. Jane Saggarwal 3327
Y10800543 YEARLY MARS LIVE SMART Mr Syed husain Jasmeet 800
Y10800543 YEARLY MARS PLAN Vriddhi Mr Rajesh babr 1000.69
Y10800543 YEARLY MARS PLAN Vriddhi Mr. Rajan visal 241.53
;;;;
   run;
Linlin
Lapis Lazuli | Level 10

Hi Uma Shanker Saini,

I could not explain better than you did. Thank you!Smiley Happy

umashankersaini
Quartz | Level 8

Linlin : Thank you so much for your appreciation and for excellent answer.

AnandSahu
Calcite | Level 5

marvelous answer....

umashankersaini
Quartz | Level 8

awesome.... answer....

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
  • 11 replies
  • 1327 views
  • 9 likes
  • 6 in conversation