I'm too impatient.  Do this:   Create a format to collapse data as desired. Use PROC SUMMARY to collapse your data using formatted input. Here's some test code.  I leave it to you to research PROC SUMMARY and its support of formats to create groups.   data have; length code 8; input code; datalines; 362 367 368 369 378 930 362 930 362 367 368 369 378 930 362 367 368 369 378 930 ; run; proc format; value code 367-369='367-369' ; run; data want; set have; group=put(code,code.-L); run; ... View more

Try again.  Cut-and-paste your data into SAS.  Does it give the correct results?  For me, I get a bunch of missing records.   Hint: Wrap your code via "Insert SAS Code". ... View more

Edit your post with self-contained data steps using datalines.  Don't make us do your job converting your post into usable code. ... View more

@JJP1 wrote: Hi, Actually we cretead parametrised SAS DI job, in it we used userwritten node code to kill the work datasets. and this parameterised job is being used  in loop job and running the loop job based on year by year ,so when we run loop job  we are getting error as below   ERROR: File WORK.WWWWWW.DATA does not exist ERROR: File WORK.WWWWWW.DATA does not exist.   loop job work fine with out user written node(where we are killing work tables ) please help.but we do not want work tables to be stored   Provide the code and/or the SAS log where you're getting these errors.  PROC DATASETS LIB=WORK KILL NOWARN NOLIST will not generate this error for non-existent data sets.   (Interesting naming convention for your dataset names 😉 ) ... View more

@Jagadishkatam 's approach is similar to mine, and I think is a clearer, cleaner approach. ... View more

data have; input Patient $ Visit Variable $ HT WT BI; cards; A 1 H 1 . . A 2 H 3 . . A 3 H 4 . . A 1 W . 2 . A 2 W . 5 . A 3 W . 9 . A 1 B . . 0 A 2 B . . 8 A 3 B . . 8 B 1 H 1 . . B 2 H 3 . . B 3 H 1 . . B 1 W . 2 . B 2 W . 4 . B 3 W . 1 . B 1 B . . 0 B 2 B . . 0 B 3 B . . 8 ; run; data want; input Patient $ Visit Variable $ HT WT BI; cards; A 1 H 1 2 0 A 2 H 3 5 0 A 3 H 4 9 8 A 1 W 1 2 0 A 2 W 3 5 8 A 3 W 4 9 8 A 1 B 1 2 0 A 2 B 3 5 8 A 3 B 4 9 8 B 1 H 1 2 0 B 2 H 3 4 0 B 3 H 1 1 8 B 1 W 1 2 0 B 2 W 3 4 0 B 3 W 1 1 8 B 1 B 1 2 0 B 2 B 3 4 0 B 3 B 1 1 8 ; run; proc summary data=have nway; class Patient Visit; var HT WT BI; output out=max max=; run; data test; * set PDV order (optional) ; if 0 then set have; * initialize &_hashnum_ to zero then declare hash objects ; %let _hashnum_=0; %hash_define(data=max, keys=Patient Visit, vars=HT WT BI); set have; %hash_lookup; drop _rc:; run; proc compare base=want comp=test; run; Do you have a typo in your want dataset?   Alternate approach:   data have; input Patient $ Visit Variable $ HT WT BI; cards; A 1 H 1 . . A 2 H 3 . . A 3 H 4 . . A 1 W . 2 . A 2 W . 5 . A 3 W . 9 . A 1 B . . 0 A 2 B . . 8 A 3 B . . 8 B 1 H 1 . . B 2 H 3 . . B 3 H 1 . . B 1 W . 2 . B 2 W . 4 . B 3 W . 1 . B 1 B . . 0 B 2 B . . 0 B 3 B . . 8 ; run; data want; input Patient $ Visit Variable $ HT WT BI; cards; A 1 H 1 2 0 A 2 H 3 5 0 A 3 H 4 9 8 A 1 W 1 2 0 A 2 W 3 5 8 A 3 W 4 9 8 A 1 B 1 2 0 A 2 B 3 5 8 A 3 B 4 9 8 B 1 H 1 2 0 B 2 H 3 4 0 B 3 H 1 1 8 B 1 W 1 2 0 B 2 W 3 4 0 B 3 W 1 1 8 B 1 B 1 2 0 B 2 B 3 4 0 B 3 B 1 1 8 ; run; proc summary data=have nway; class Patient Visit; var HT WT BI; output out=max max= / autoname; run; data test; * set PDV order (optional) ; if 0 then set have; * initialize &_hashnum_ to zero then declare hash objects ; %let _hashnum_=0; %hash_define(data=max, keys=Patient Visit, vars=HT_Max WT_Max BI_Max); set have; %hash_lookup; array vars{*} HT WT BI; array max{*} HT_Max WT_Max BI_Max; do i=1 to dim(vars); if missing(vars{i}) then vars{i}=max{i}; end; drop _rc: i HT_Max WT_Max BI_Max; run; proc compare base=want comp=test; run; See  https://github.com/scottbass/SAS/blob/master/Macro/hash_define.sas and  https://github.com/scottbass/SAS/blob/master/Macro/hash_lookup.sas   If your data differs from what you posted then this approach may not work.   (Can you add "run;" statements to your have and want data steps as best practice?)   ... View more

Try:   proc datasets library=work kill nowarn nolist; run; quit; ... View more

I use them to create summary tables. For example, using Variable and Value, I can pull it into a macro and get a sum by each variable   Are you familiar with proc summary and BY group processing?   Post your macro and desired output from that macro.   And yes I have edited it so that my 'want' output is more clear.   data want;<br />merge have1 have2;<br />by Patient Visit;<br />run;<code></code><code></code> Really?  (This is minor, just attention to detail.) ... View more

      data _null_;          set sashelp.stocks;          call symput ("nofobs", _n_);       run;   What if sashelp.stocks is 100M records?  You're going to call symput() 100M times, overwriting nofobs 100M times, only to save the last result.  That is, ahem, "inefficient".   Number of observations != empty/not empty.   Have a look at  https://github.com/scottbass/SAS/blob/master/Macro/nobs.sas or  https://github.com/scottbass/SAS/blob/master/Macro/check_if_empty.sas   /*once those three text file done, want to touch empty 3 file */ /*some logic */   Why do you need to create three marker files?  If this is just to control downstream processing, then isn't one marker file enough?   Are the marker files meant to be the same name as your exported files?  If so, of course create them before your export, otherwise they will overwrite your exported SAS files.   FWIW, I usually implemented the logic:   If marker file exists then ETL is running, don't run downstream code. If marker file does not exist then upstream ETL was successful, run downstream code.   I then create the "ETL is running" marker file at the beginning of the ETL, and delete the marker file when the success condition is met.  The downstream ETL loops while the marker file exists.   Then if I come in the morning and the marker file exists I know where the error occurred.  Of course, the email tells me that too :).  I then fix the issue, manually delete the marker file, then re-run the downstream ETL.   Finally, you might be able to steal some of the logic in here: https://github.com/scottbass/SAS/blob/master/Macro/lock.sas https://github.com/scottbass/SAS/blob/master/Macro/marker.sas   It's not exactly what you're looking for but may give you some ideas...     ... View more

@serena13lee wrote: It's a bit complex but I am trying to keep as much of Have1 because I have other calculations that depend on that dataset format. I assume your 2nd "have1" is really your "want"?  (Minor:  also clean up your "want" proposed code).   IMO that's a really strange format that I rarely see.  Can you give details on your "...other calculations that depend on that dataset format"?   Edit:  My "gut feeling" is you want to store and process your data like this.  But without knowing the details of your "other calculations" I can't be sure.   data have; input Patient $ Variable $ Value Visit $; cards; 1 Height 1 a 1 Weight 2 a 1 Head 1 a 1 BMI 5 a <<< 1 Height 3 b 1 Weight 4 b 1 Head 5 b 1 Feet 6 b 1 BMI 5 b <<< 2 Height 3 c 2 Weight 2 c 2 Head 6 c 2 BMI 4 c <<< ; run; proc transpose data=have out=want; by patient visit; var value; id variable; run; I'm also curious how you are creating your "have2" table? ... View more

P.S.:  Read the documentation on PROC TRANSPOSE. ... View more