topic Re: Call data using Cards/dataline option in SAS Programming

Call data using Cards/dataline option

sunilzood — Tue, 02 Jul 2013 15:17:04 GMT

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

Re: Call data using Cards/dataline option

AncaTilea — Tue, 02 Jul 2013 17:59:26 GMT

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.

Re: Call data using Cards/dataline option

Linlin — Tue, 02 Jul 2013 18:26:05 GMT

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;

Re: Call data using Cards/dataline option

sunilzood — Wed, 03 Jul 2013 02:02:21 GMT

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

Re: Call data using Cards/dataline option

sunilzood — Wed, 03 Jul 2013 02:03:33 GMT

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

Re: Call data using Cards/dataline option

AnandSahu — Wed, 03 Jul 2013 06:06:45 GMT

marvelous answer....

Re: Call data using Cards/dataline option

umashankersaini — Wed, 03 Jul 2013 06:18:04 GMT

awesome.... answer....

Re: Call data using Cards/dataline option

umashankersaini — Wed, 03 Jul 2013 06:56:07 GMT

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

Re: Call data using Cards/dataline option

sunilzood — Wed, 03 Jul 2013 08:19:57 GMT

Understood!!! Thanks Dude.

Re: Call data using Cards/dataline option

data_null__ — Wed, 03 Jul 2013 11:33:17 GMT

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;

Re: Call data using Cards/dataline option

Linlin — Wed, 03 Jul 2013 12:33:28 GMT

Hi Uma Shanker Saini,

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

Re: Call data using Cards/dataline option

umashankersaini — Wed, 03 Jul 2013 17:43:44 GMT

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