Hello LisaEb, Is this what you need?: [pre] data i; input Entered mmddyy10. Br $ Grp $ Name1 $ Name2 $ id srvc $ Agt; format Entered date7.; datalines; 11/03/2010 LA CP Lisa Eberman 12345 LA 6782 11/03/2010 LA CP Lisa Eberman 12345 LA 6166 11/03/2010 LA RP Lisa Eberman 12345 LA 6686 11/03/2010 LA RP Lisa Eberman 12345 LA 6633 11/03/2010 LA RP Lisa Eberman 12345 LA 73660 11/03/2010 LA RP Lisa Eberman 12345 LA 6231 11/03/2010 LA AP Lisa Eberman 12345 LA 1234 11/03/2010 NY RP Ella Temp 56789 LA 6231 11/03/2010 NY RP Ella Temp 56789 LA 6232 11/03/2010 NY MM Mark Wise 09876 NY 64621 11/03/2010 NY MM Mark Wise 09876 NY 64605 11/03/2010 NY MM Mark Wise 09876 NY 64589 11/03/2010 NY MM Mark Wise 09876 NY 64588 11/03/2010 NY MM Mark Wise 09876 NY 64586 11/03/2010 NY AB Liza White 19876 XY 64588 11/03/2010 NY AB Liza White 19876 XY 64586 ; run; proc sort data=i out=s; by Entered Br Grp Name1 Name2 id srvc; run; proc transpose data=s out=r(drop=_name_) prefix=Agt; var agt; by Entered Br Grp Name1 Name2 id srvc; run; [/pre] Sincerely, SPR ... View more

Hello Wouther, "But... I have to deal now with 2 variables (month and year). Besides this, the directory will change also --> I think building a macro around it (to cover the problem of the 2 variables: month & year) will not do the job, because the dataset I want to create ("Test" in this case) will be overwritten everytime a new dataset from another directory is read by the set commando (but on the other hand, I need the set commando...)...?" First of all with small change of your code the output dataset will NOT be overwritten: [pre] %macro combine; data test; set %do i = 1 %to 2; file&i %end; ; run; %mend; %combine [/pre] I moved ; from file&i to the place before run. After that a macro loop on year and month will help perfectly. Sincerely, SPR ... View more

Hello Weiz, Try [pre] dbms=XLS replace; [/pre] Sincerely, SPR ... View more

Hello 5557691, I've found a standard approach to the problems of this kind (code not tested, sample is necessary): [pre] /* Categories and Descriptions into macro variables */; proc SQL noprint; select distinct category into :n from skuinfo1; %let n=%trim(&n); select category into :c1-:c&n from skuinfo1; select catgdesc into :d1-:d&n from skuinfo1; quit; /* Loop on caterories and descriptions */; %macro a; %local i catg catgdesc; %do i=1 %to &n; %let catg=&&c&i; %let catgdesc=&&d&i; %end; %mend a; %a [/pre] Sincerely, SPR ... View more

Hello Activa, This is a possible solution: [pre] data i; id=1; ssn=100; output; id=1; ssn= 90; output; id=1; ssn= 80; output; id=2; ssn=200; output; id=3; ssn=200; output; id=4; ssn=400; output; run; proc sort data=i out=s; by ID; run; data BadID; set s; if NOT (First.ID=1 and Last.ID=1); by ID; run; proc sort data=i out=s; by SSN; run; data BadSSN; set s; if NOT (First.SSN=1 and Last.SSN=1); by SSN; run; [/pre] Sincerely, SPR ... View more

Hello SASPhile, It could look something like this: [pre] %let m=S:\Per\IMS\SAS\INFILES\Jan 2010 ZUD.txt; %let mt=%SUBSTR(%SCAN(&m,-1,%STR(\)),1,3); %let yr=%SUBSTR(%SCAN(&m,-1,%STR(\)),5,4); %put mt=&mt yr=&yr; data _null_; length m $2; date="01&mt.&yr."d; format date date7.; m=put(MONTH(date),2.); if month(date) < 10 then m="0"||m; call symputx('mon',m); run; %put mon=&mon yr=&yr; %let datamon=%SYSEVALF(&yr*100+&mon); %let currmon=&yr.-&mon.-01; %put datamon=&datamon currmon=&currmon; [/pre] Sincerely, SPR ... View more

Hello Siddhatha, I agree with Cynthia that this task is for procs REPORT or TABULATE. Anyway the major part of the possible solution could be like this: [pre] proc means data=outst_amt noprint; output out=t1 (drop=_freq_) Sum=S; var out_amount; class cus_cat prd_cat date_values; run; proc transpose data=t1 (where=(_type_=5)) out=r1 (drop=_name_) prefix=_; ID date_values; var S; by cus_cat; run; proc transpose data=t1 (where=(_type_=3)) out=r2 (drop=_name_) prefix=_; ID date_values; var S; by prd_cat; run; [/pre] Sincerely, SPR ... View more

Hello MarcTC, I think that this can be achieved by formats. However, you still have to determine all rules to identify possible variations. For example, for street: [pre] proc format; value $st "ST."="Street" "ST"="Street" "Street"="Street" ; run; [/pre] Sincerely, SPR ... View more

Hello Suddhartha, This is another solution (pure dataset operations): [pre] data i; length prd_grp prd_subgroup prd_desc $15; input prd_grp $ prd_subgroup $ prd_desc $ loan_amount; datalines; ASSETS All_Other Loans 234 ASSETS All_Other abc 345 ASSETS All_Other Quoted_Shares 213 LIABILIITES Deposits Current_Deposs 435 LIABILIITES Deposits Fixed 325 LIABILIITES Deposits NIDs_&_Repos 256 LIABILIITES Deposits Savings 218 ; run; data t; retain prd_grp prd_subgroup prd_desc loan_amount total grand_total pr; set i end=ie; if _n_=1 then grand_total=0; if first.prd_grp then do; pr=1; output; end; if first.prd_subgroup then do; pr=2; output; total=0; end; pr=3; output; total+loan_amount; if last.prd_subgroup then do; pr=4; output; end; grand_total+loan_amount; by prd_grp prd_subgroup; keep prd_grp prd_subgroup prd_desc loan_amount total grand_total pr; if ie then do; pr=5; output; end; run; data r(keep=Item Loan_Amount); retain Item Loan_Amount; set t; if pr=1 then do; Item=prd_grp; loan_amount=.; end; if pr=2 then do; Item=prd_subgroup; loan_amount=.; end; if pr=3 then do; Item=prd_desc; end; if pr=4 then do; Item="Total"; loan_amount=Total; end; if pr=5 then do; Item="Grand Total"; loan_amount=grand_Total; end; run; [/pre] Sincerely, SPR ... View more

Hello Darrylovia, I had the same problem and to solve it created a copy of inventory variable, applied format to it and used as a class variable: [pre] data i; set sashelp.shoes; inv=inventory; run; proc summary data=i nway; class inv; format inv quartilefmt.; var inventory; output out=inventory_by_quartiles mean=mean std=std; run; [/pre] I should also notice that without NWAY option you will get total mean and std that could be useful. Sincerely, SPR ... View more

Hello SASgeek, I agree with all previous posts and can add that the course on MACRO PROGRAMMING is necessary too. Sincerely, SPR ... View more

Hello SASPhile, You can use even more simple logic: if NOT (a and b); like this: [pre] data a; x=1; output; x=2; output; run; data b; x=3; output; x=2; output; run; proc sort data=a; by x; run; proc sort data=b; by x; run; data dif; merge a (in=a) b(in=b); if not (a and b); by x; run; [/pre] Sincerely, SPR ... View more

Hello Ksharp, I did not see mocking remarks on my code from Chang_y_chung. However, I tried to optimize my code and got the following result:[pre] data i; do l=1 to 500000; i=1; a=1; b=1; c="a*b"; output; i=2; a=1; b=1; c="a+b"; output; end; run; options nomlogic nosymbolgen; proc SQL ; select COUNT(*) as n into :n from i; quit; %let n=%TRIM(&n); %macro b(p=); %do i=&p %to &n %by &p; data a; set i; if %EVAL(&i-&p) < _n_ <= &i then output; run; proc SQL noprint; select COUNT(*) as n into :n1 from a; %let n1=%TRIM(&n1); select c into :c1-:c&n1 from i; quit; data b; set a; %do j=1 %to &n1; if &j EQ _n_ then do; d=&&c&j; end; %end; run; %if &i=&p %then %do; data r; set b; run; %end; %else %do; data r; set r b; run; %end; %end; %mend b; %b(p=5000); ; It does not compete with Chang's code and runs about 5 minutes for 1M observations. Sincerely, SPR ... View more

Hello Sandhya, If I understood you correctly then this is solution (not simple one): [pre] data i; input ID ADATE MMDDYY10. SERVICE $; format adate date7.; date=adate; datalines; 1 12/7/2009 I 2 1/12/2009 I 2 1/13/2009 OP 2 1/13/2009 OP 3 6/4/2009 OP 3 6/5/2009 OP 4 7/29/2009 LA 5 3/23/2009 OP ; run; proc sort data=i out=s; by ID adate; run; proc SQL noprint; select COUNT(distinct ID) into :n from i; %let n=%TRIM(&n); select distinct ID into :id1-:id&n from i; quit; %macro a; %local i j k k1; %do i=1 %to &n; proc sql noprint; select count(*) into :nd from s where ID=&&ID&i; %let nd=%TRIM(&nd); select date into :d1-:d&n from s where ID=&&ID&i; select service into :s1-:s&n from s where ID=&&ID&i; quit; data dr&i; length service $2; %do j=1 %to &nd; %if &&s&j NE I %then %do; %do k=&j %to 1 %by -1; %if &k GT 1 %then %do; if &&d&j = &&d&k OR %EVAL(&&d&j - &&d&k) = 1 then %let s&j=I; %end; %end; %end; id=&&ID&i; adate=&&d&j; service="&&s&j"; format adate date7.; output; %end; run; %if &i = 1 %then %do; data r; retain id adate service; set dr&i; run; %end; %else %do; data r; set r dr&i; run; %end; %end; %mend a; %a; [/pre] Sincerely, SPR ... View more

Hello Chang_y_chung@hotmail.com, It was typo from my side. Of course, comma is forbidden. The rest is true. RETAIN can be used to order variables in the dataset. Sincerely, SPR ... View more