I updated my previous code to the new data    data have; input id timeorder clinic total totalsquare; cards; 1 20020408 12135 1 1 1 20020715 12135 2 4 1 20030124 15109 1 1 1 20030308 75423 1 1 1 20030505 75423 2 4 1 20030607 75423 3 9 1 20030815 12135 3 9 2 20020615 25875 1 1 2 20020717 25875 2 4 2 20020824 55415 1 1 2 20020907 25875 3 9 2 20021215 25875 4 16 2 20030108 25875 5 25 ; proc sql noprint; select distinct cats('_',clinic) into: clinic1 separated by ' ' from have; select distinct cats(clinic) into: clinic2 separated by ' ' from have; quit; %put &clinic1; %put &clinic2; options symbolgen; data want; retain &clinic1.; set have; by id notsorted; array alp(*) &clinic1.; array alpc(&sqlobs) (&clinic2.); do i = 1 to dim(alp); if first.id then alp(i)=.; if totalsquare ne . and clinic=alpc(i) then alp(i)=totalsquare; end; sum=sum(of alp(*)); run; ... View more

did you try the import procedure?   proc import datafile='F:\B04a.csv' out=want dbms=csv replace; run; ... View more

please try the robust approach with macro variables   %put &clinic1; %put &clinic2;   data have; input id timeorder clinic:$ total totalsquare; cards; 1 1 a 1 1 1 2 a 2 4 1 3 b 1 1 1 4 b 2 4 1 5 a 3 9 1 6 c 1 1 ; proc sql noprint; select distinct clinic into: clinic1 separated by ' ' from have; select distinct cats("'",clinic,"'") into: clinic2 separated by ' ' from have; quit; %put &clinic1; %put &clinic2; data want; retain a b c; set have; by id notsorted; array alp(*) &clinic1; array alpc(&sqlobs)$ (&clinic2.); do i = 1 to dim(alp); if first.id then alp(i)=.; if totalsquare ne . and clinic=alpc(i) then alp(i)=totalsquare; end; sum=sum(of alp(*)); run; ... View more

data have; input date :$3. id first; cards; MAY 1 1 MAY 1 . JUN 1 . SEP 2 1 SEP 2 . ; proc sort data=have; by date; run; data want; do until(last.date); set have; by date; retain cnt; if first.date then cnt=1; else cnt+1; end; do until(last.date); set have; by date; if cnt>1 then flag=1; else flag=.; output; end; run; ... View more

try the perl expression   data have; input name &:$200.; id=strip(prxchange('s/(.*)(gsr)(\d+)(.*)/$3/',-1,name)); Emp_Name=strip(prxchange('s/(.*)(gsr\d+)(.*)/$1$3/',-1,name)); cards; sriram bhayagari gsr4455 naveen gsr5566 allapuram gsr6677 srinath thatipathri ; ... View more

by call execute   data have; infile cards dlm='09'x; input id$ spend lend; cards; a 10.1 0.5 a 5.5 7 b 1.2 2 c 3 6 b 6 3.1 ; proc sort data=have out=distinct(keep=id) nodupkey; by id; run; data _null_; set distinct; call execute("data "||id||";set have;if id='"||id||"';output "||id||"; run;"); run; ... View more

data have; infile cards dlm='09'x; input id$ spend lend; cards; a 10.1 0.5 a 5.5 7 b 1.2 2 c 3 6 b 6 3.1 ; proc sql; select distinct id into : val separated by ' ' from have; quit; %put &sqlobs; %macro test; %do i = 1 %to &sqlobs; %let outs=%scan(&val,&i); data &outs; set have; if id="&outs"; run; %end; %mend; %test; ... View more