Hey Scott, Thanks for looking into my problem. Really appreciate the answer but im not looking for an alternative solution to what i have coded. I wanted to knw about the piece of code written in the SQL which uses the pass through facility technique specified in my original post. The temporary table that you asked about is table with unique primary key created with no issues and Im executing the code using a Batch job. Thanks! ... View more

Hi SAS09, A simple do loop with WEEKDAY function can also solve you problem. data dx; input cretdt YYMMDD10.; do i = 0 to 7 until(weekdaynum in(1,7)) ; weekdaynum=weekday(cretdt + i); weekend_date=cretdt+i; end; format cretdt weekend_date YYMMDD10.; datalines; 2009-04-03 2009-04-09 2009-04-15 ; run; proc print;run; ... View more

Hey tangentray, _Null_ is right about the compound index thing. But if you have these 2 indexes already created and maintained and you're not allowed to create extra indexes, then i believe creating compound index of both the variables for just this thing/matching would be a problem for you. If you're doing a lookup on a large dataset ( data B) then i believe format technique of lookup can help( check out paper on SGF 2009 :: Proc Format, a Speedy Alternative to Sort Sort Merge :: http://support.sas.com/resources/papers/proceedings09/064-2009.pdf ) Back when i was learning about the set with Key= option, I wrote something that matches with your multiple index problem ( though not exactly the solution you would want as KEY= Multiple simple indexS is not wht i wrote, but KEY=single simple index with multiple SET statement ). I believe this code would increase the CPU time( if you've read the set ith key= theory you would knw why the increase in time), but the increase is depended on the data and the index variables being sorted/unsorted before they were made index. data master(index=(PartNumber quantity )); input PartNumber quantity; datalines; 100 10 200 20 300 30 400 40 500 50 ; data description(index=(PartNumber quantity )); input PartNumber quantity partdescription $; datalines; 400 40 Nuts 300 30 Bolts 200 10 Screws 600 90 Washers 500 50 Bashers ; data extract; set description; /* smaller dsn, transaction dsn*/ set master(drop=quantity) key=PartNumber ; /* larger dsn*/ length errormessage $200.; drop errormessage; select (_iorc_); when(%sysrc(_sok)) do; /* A match was found */ PartNumberX=PartNumber; set master key=quantity ; select (_iorc_); when(%sysrc(_sok)) do; /* A match was found */ if PartNumberx=PartNumber then output; else delete; end; when (%sysrc(_dsenom)) do; /* No match was found */ _error_ = 0; end; otherwise do; errormessage = iorcmsg(); put "ATTENTION: unknown error condition: " errormessage; end; end; end; when (%sysrc(_dsenom)) do; /* No match was found */ _error_ = 0; end; otherwise do; errormessage = iorcmsg(); put "ATTENTION: unknown error condition: " errormessage; end; end; run; proc print;run; Thanks! ... View more

Hey Susan, the do loop is making the datasets out1 out2 out3 created for i=1 for i=2 /3 the datasets are replaced, thats why the data1 /2 is coming out as 0 for i=1 data OUT1 OUT2 OUT3; set IN; select(1); when(1) do; if x='A' then count1+1; output out1; end; when(2) do; if x='B' then count2+1; output out2; end; when(3) do; if x='C' then count3+1; output out3; end; otherwise; end; for i=2 data OUT1 OUT2 OUT3; set IN; select(2); when(1) do; if x='A' then count1+1; output out1; end; when(2) do; if x='B' then count2+1; output out2; end; when(3) do; if x='C' then count3+1; output out3; end; otherwise; end; for i=3 data OUT1 OUT2 OUT3; set IN; select(3); when(1) do; if x='A' then count1+1; output out1; end; when(2) do; if x='B' then count2+1; output out2; end; when(3) do; if x='C' then count3+1; output out3; end; otherwise; end; As you said the that the only counting is done when i=3, and you get the number of records with x='C' in data set IN, and I get that correctly in OUT3. OUT1 and OUT2 have 0 observations. This is because the dataset OUT1/2 is getting reused by the last do loop (i=3). thats why the count1/2 =0 ... View more

Hi Qiqi, try making a counter which is set to some value say =0 and as soon as the name='F' is encountered change the value of the counter to say = 1. Use the output statement based on that counter. data test; input name:$4. data1:$char10. ; cards; A dummy B dummy C dummy D dummy E dummy F dummy G dummy H dummy ;;;; run; data d1(drop=counter); set test; retain counter; if _n_=1 then counter=0; if name ='F' then counter=1; if counter=1 or name='F'; run; proc print;run; ... View more

Hi Pavan, Here is a paper from SAS Global Forum 2007 Tutorials :: http://www2.sas.com/proceedings/forum2007/213-2007.pdf , try going through it. Sushil ... View more

Hey Null/Cynthia, Just found this :: http://support.sas.com/kb/33/814.html ... a basic solution to my problem. but not with proc transpose...but a different approach..kinda like it Thanks to SAMPLES & SAS NOTES for the solution :: data visits; input dovisit date9. person_id sex :$1. nvisit :$1. fvisit :$1. avisit :$1.; datalines; 18dec2007 444 M T F F 18dec2007 444 M T F F 10jan2007 365 M T F F 10jan2007 365 M T F F 01feb2007 212 F T T T 01feb2007 212 F T F T ; run; /*create a data set of the duplicates using DUPOUT= option */ proc sort data=visits dupout=visits_dup nodupkey; by person_id; run; /* Create a macro variable with the variable names that are to */ /* be merged. The variables considered BY variables are excluded */ /* from going into the macro variable using the NOT IN operator. */ /* The resulting macro variable is in the format varname=varname_2 */ */ proc sql noprint; select trim(name) || '=' || trim(name) || '_2' into :varlist separated by ' ' from DICTIONARY.COLUMNS WHERE LIBNAME EQ "WORK" and MEMNAME EQ "VISITS" and upcase(name) not in ('PERSON_ID' 'DOVISIT' 'SEX'); quit; /*Merge the two data sets using the macro variable to rename the */ /*common variables in the second (duplicates) data set. */ data merged; merge visits visits_dup (rename=(&varlist)); by person_id; run; proc print; run; ... View more

Hey Krishna, Try this out data dx; infile datalines missover; input NAME :$10. AGE :3. GENDER :$1. RELATION :$10.; datalines; A 40 M HUSBAND B 30 F sPOUSE C 10 M CHILD D 40 M HUSBAND E 30 F SPOUSE F 10 M CHILD ; data dxx; set dx; retain counter 0; if strip(relation) = 'HUSBAND' then counter=counter+1 ; run; proc summary nway missing data= dxx; class counter ; output out=sushil(drop=_: relation_2 relation_3 gender_2 gender_3 age_2 age_3 counter) idgroup( out{3} (NAME AGE GENDER RELATION)=); run; proc print data=sushil;run; if you don't like proc summary, you can do datastep manipulation after the dxx dataset creation. I kinda love summary procedure, hence used it over here. hope that helps 🙂 ... View more

Hey tzsmile, you forgot to put the by variable in the datastep... proc sort data=p05; by code ; run; data pp05; set p05; by code ; if FIRST.code ; run; just add "by code ;" in the datastep like i have done in the above code and you'll see your code without any errors/ warnings. ... View more

Hey Null 🙂 , gr8 solution. Honestly i never thought of proc summary to solve this problem....wow!!..solves my problem with just one time usage of proc sql and proc summary. Thanks! ... View more

Hey Sonny, Jaroslav is right!!!. the && would solve your problem. example:: %let fruit1=Apple; %let fruit2=Orange; %let fruit3=Banana; %let n=2; proc means data=fruits; title "Sales for fruit: &&section&n"; where section="&&section&n"; var saleprice; run; After macro variable resolution, the preceding program becomes: proc means data=fruits; title "Sales for fruit: Orange"; where section="Orange"; var saleprice; run; There are certain rules that macro processor follows to resolve macro variable references that contain multiple ampersands > Macro variable references are resolved from left to right. > Two ampersands (&&) resolve to one ampersand (&). > Multiple leading ampersands cause the macro processor to rescan the reference until no more ampersands can be resolved. You can take advantage of these rules to create new macro variable references. Hope that helps 🙂 ... View more

Hi Cynthia, Thanks for looking into my problem. I totally agree with the datastep solution that you have given, but i was wondering if this sort of outcome can be possible using PROC TRANSPOSE only ONCE. Just wanted confirm this up, as i believe this can't be done using PROC TRNASPOSE only once. Thanks! ... View more