Call data using Cards/dataline option

[sunilzood]

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

[AncaTilea]

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]

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

[Linlin · Accepted solution]

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]

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]

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]

Understood!!! Thanks Dude.

[data_null__]

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]

Hi Uma Shanker Saini,

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

[umashankersaini]

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

[AnandSahu]

marvelous answer....

[umashankersaini]

awesome.... answer....