Call data using Cards/dataline option

Accepted Solution Solved
Reply
Contributor
Posts: 29
Accepted Solution

Call data using Cards/dataline option

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


Accepted Solutions
Solution
‎07-02-2013 02:26 PM
Super Contributor
Posts: 1,636

Re: Call data using Cards/dataline option

Posted in reply to sunilzood

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


All Replies
Super Contributor
Posts: 543

Re: Call data using Cards/dataline option

Posted in reply to sunilzood

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.

Contributor
Posts: 29

Re: Call data using Cards/dataline option

Posted in reply to AncaTilea

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

Solution
‎07-02-2013 02:26 PM
Super Contributor
Posts: 1,636

Re: Call data using Cards/dataline option

Posted in reply to sunilzood

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;

Contributor
Posts: 29

Re: Call data using Cards/dataline option

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

Frequent Contributor
Posts: 118

Re: Call data using Cards/dataline option

Posted in reply to sunilzood

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


Contributor
Posts: 29

Re: Call data using Cards/dataline option

Posted in reply to umashankersaini

Understood!!! Thanks Dude.

Respected Advisor
Posts: 3,799

Re: Call data using Cards/dataline option

Posted in reply to sunilzood

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;
Super Contributor
Posts: 1,636

Re: Call data using Cards/dataline option

Posted in reply to umashankersaini

Hi Uma Shanker Saini,

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

Frequent Contributor
Posts: 118

Re: Call data using Cards/dataline option

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

Frequent Contributor
Posts: 81

Re: Call data using Cards/dataline option

marvelous answer....

Frequent Contributor
Posts: 118

Re: Call data using Cards/dataline option

awesome.... answer....

🔒 This topic is solved and locked.

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

Discussion stats
  • 11 replies
  • 415 views
  • 9 likes
  • 6 in conversation