Sorry, if I am confusing you. But my concern is not about first. or last. my concern is that it is not checking consecutive pramn is presented or not, which means my code flags all the consecutive record if there is any paramn is missing in between in same by group. ... View more

I have provided the data I have and I have provided the data I want. My code is flagging all the consecutive records and it is not checking paramn is consecutive or not. ... View more

Hi @Tom , thanks for your reply! what if we need to check if there consecutive paramcd is there or not? I mean we need to flag if there are consecutive paramcd's without missing.   Thanks, Adithya ... View more

Thanks for your reply! I have one more situtation, if we have multiple consecutive 5 2's in a same group of parcat1 and avisit and usubjid then we need to flag only first record instead of flagging every first record in each consecutive group. How can we do that, I can provide example data if I am confusing. ... View more

I have added expected output now in my post. Thanks! ... View more

Hi, Please find the data below. data have; infile datalines dsd truncover; input USUBJID:$30. ANAGR2N:32. PARAM:$200. PARAMCD:$8. AVALCAT1:$200. AVISIT:$200. AVISITN:32. avaln:32. BASECAT1:$200. basen:32.; datalines4; TAK-935-3001-09011-105,1,Mobility,EQ5D0201,No problems,V1,1,1,Missing,6 TAK-935-3001-63004-105,1,Mobility,EQ5D0201,No problems,V1,1,1,Missing,6 TAK-935-3001-09011-105,1,Mobility,EQ5D0201,Total,V1,1,9,Missing,6 TAK-935-3001-63004-105,1,Mobility,EQ5D0201,Total,V1,1,9,Missing,6 TAK-935-3001-09011-105,1,Mobility,EQ5D0201,No problems,V1,1,1,Total,9 TAK-935-3001-63004-105,1,Mobility,EQ5D0201,No problems,V1,1,1,Total,9 TAK-935-3001-09011-105,1,Mobility,EQ5D0201,Total,V1,1,9,Total,9 TAK-935-3001-63004-105,1,Mobility,EQ5D0201,Total,V1,1,9,Total,9 TAK-935-3002-02001-202,2,Mobility,EQ5D0201,No problems,V1,1,1,No problems,1 TAK-935-3002-04001-202,2,Mobility,EQ5D0201,No problems,V1,1,1,No problems,1 TAK-935-3002-04003-202,2,Mobility,EQ5D0201,No problems,V1,1,1,No problems,1 TAK-935-3002-04003-203,2,Mobility,EQ5D0201,No problems,V1,1,1,No problems,1 TAK-935-3002-07001-201,2,Mobility,EQ5D0201,No problems,V1,1,1,No problems,1 TAK-935-3002-07001-204,2,Mobility,EQ5D0201,No problems,V1,1,1,No problems,1 TAK-935-3002-09001-203,2,Mobility,EQ5D0201,No problems,V1,1,1,No problems,1 TAK-935-3002-09001-204,2,Mobility,EQ5D0201,No problems,V1,1,1,No problems,1 TAK-935-3002-09002-203,2,Mobility,EQ5D0201,No problems,V1,1,1,No problems,1 TAK-935-3002-09002-204,2,Mobility,EQ5D0201,No problems,V1,1,1,No problems,1 TAK-935-3002-09003-203,2,Mobility,EQ5D0201,No problems,V1,1,1,No problems,1 TAK-935-3002-09003-206,2,Mobility,EQ5D0201,No problems,V1,1,1,No problems,1 ;;;; run; And here is the log for my above code, there is no error, I am just getting 0 records. 1 OPTIONS NONOTES NOSTIMER NOSOURCE NOSYNTAXCHECK; INFO: Character variables have defaulted to a length of 200 at the places given by: (Line):(Column). Truncation can result. 40:1 RC 72 73 data adqseq1_fin; 74 set adqseq1_fin3; 75 output; 76 anagr2="Total"; 77 anagr2n=3; 78 output; 79 run; NOTE: There were 7,680 observations read from the data set WORK.ADQSEQ1_FIN3. NOTE: The data set WORK.ADQSEQ1_FIN has 15,360 observations and 17 variables. NOTE: Compressing data set WORK.ADQSEQ1_FIN decreased size by 87.01 percent. Compressed is 40 pages; un-compressed would require 308 pages. NOTE: DATA statement used (Total process time): real time 0.02 seconds user cpu time 0.02 seconds system cpu time 0.00 seconds memory 997.03k OS Memory 55204.00k Timestamp 10/27/2023 06:17:42 PM Step Count 861 Switch Count 5 Page Faults 0 Page Reclaims 19 Page Swaps 0 Voluntary Context Switches 19 Involuntary Context Switches 1 Block Input Operations 0 Block Output Operations 5128 80 proc sort nodupkey; by studyid usubjid paramcd avisitn adt anagr2n avaln basen; run; NOTE: There were 15,360 observations read from the data set WORK.ADQSEQ1_FIN. NOTE: SAS sort was used. NOTE: 0 observations with duplicate key values were deleted. NOTE: The data set WORK.ADQSEQ1_FIN has 15,360 observations and 17 variables. NOTE: Compressing data set WORK.ADQSEQ1_FIN decreased size by 87.01 percent. Compressed is 40 pages; un-compressed would require 308 pages. NOTE: PROCEDURE SORT used (Total process time): real time 0.04 seconds user cpu time 0.03 seconds system cpu time 0.02 seconds memory 25824.18k OS Memory 79884.00k Timestamp 10/27/2023 06:17:42 PM Step Count 862 Switch Count 5 Page Faults 0 Page Reclaims 6004 Page Swaps 0 Voluntary Context Switches 17 Involuntary Context Switches 0 Block Input Operations 0 Block Output Operations 5128 81 82 ** Get count; 83 proc sort data=adqseq1_fin; by paramcd param avisitn avisit anagr2n basen avaln; run; NOTE: There were 15,360 observations read from the data set WORK.ADQSEQ1_FIN. NOTE: SAS sort was used. NOTE: The data set WORK.ADQSEQ1_FIN has 15,360 observations and 17 variables. NOTE: Compressing data set WORK.ADQSEQ1_FIN decreased size by 87.01 percent. Compressed is 40 pages; un-compressed would require 308 pages. NOTE: PROCEDURE SORT used (Total process time): real time 0.05 seconds user cpu time 0.03 seconds system cpu time 0.02 seconds memory 28130.15k OS Memory 81940.00k Timestamp 10/27/2023 06:17:42 PM Step Count 863 Switch Count 5 Page Faults 0 Page Reclaims 6561 Page Swaps 0 Voluntary Context Switches 18 Involuntary Context Switches 0 Block Input Operations 0 Block Output Operations 5128 84 proc freq data=adqseq1_fin; 85 by paramcd param avisitn avisit anagr2n basen avaln; 86 tables avalcat1*basecat1 / out=step1; 87 run; NOTE: BY line has been truncated at least once. NOTE: The above message was for the following BY group: Parameter Code=EQ5D0203 Parameter=Usual Activities Analysis Visit (N)=5 Analysis Visit=V5 Week 26 Analysis Group 2 (N)=1 basen=6 NOTE: There were 15,360 observations read from the data set WORK.ADQSEQ1_FIN. NOTE: The data set WORK.STEP1 has 979 observations and 11 variables. NOTE: Compressing data set WORK.STEP1 decreased size by 76.92 percent. Compressed is 3 pages; un-compressed would require 13 pages. NOTE: PROCEDURE FREQ used (Total process time): real time 11.45 seconds user cpu time 11.17 seconds system cpu time 0.08 seconds memory 20049.71k OS Memory 69828.00k Timestamp 10/27/2023 06:17:54 PM Step Count 864 Switch Count 240 Page Faults 0 Page Reclaims 3580 Page Swaps 0 Voluntary Context Switches 720 Involuntary Context Switches 34 Block Input Operations 1488 Block Output Operations 7944 88 89 ** Transpose basecat1; 90 proc sort data=step1; by paramcd param avisitn avisit avaln avalcat1 anagr2n; run; NOTE: There were 979 observations read from the data set WORK.STEP1. NOTE: SAS sort was used. NOTE: The data set WORK.STEP1 has 979 observations and 11 variables. NOTE: Compressing data set WORK.STEP1 decreased size by 76.92 percent. Compressed is 3 pages; un-compressed would require 13 pages. NOTE: PROCEDURE SORT used (Total process time): real time 0.00 seconds user cpu time 0.01 seconds system cpu time 0.00 seconds memory 2875.75k OS Memory 68820.00k Timestamp 10/27/2023 06:17:54 PM Step Count 865 Switch Count 11 Page Faults 0 Page Reclaims 424 Page Swaps 0 Voluntary Context Switches 42 Involuntary Context Switches 0 Block Input Operations 0 Block Output Operations 392 91 proc transpose data=step1(where=(basen ne .)) out=step2(drop=_name_ _label_) prefix=_; 92 id basen; 93 var count; 94 by paramcd param avisitn avisit avaln avalcat1 anagr2n; 95 run; NOTE: There were 781 observations read from the data set WORK.STEP1. WHERE basen not = .; NOTE: The data set WORK.STEP2 has 251 observations and 14 variables. NOTE: Compressing data set WORK.STEP2 decreased size by 33.33 percent. Compressed is 2 pages; un-compressed would require 3 pages. NOTE: PROCEDURE TRANSPOSE used (Total process time): real time 0.00 seconds user cpu time 0.01 seconds system cpu time 0.00 seconds memory 2837.59k OS Memory 68552.00k Timestamp 10/27/2023 06:17:54 PM Step Count 866 Switch Count 19 Page Faults 0 Page Reclaims 149 Page Swaps 0 Voluntary Context Switches 72 Involuntary Context Switches 0 Block Input Operations 0 Block Output Operations 408 96 97 ** Transpose ANAGR2N; 98 data step2_; 99 set step2; 100 if anagr2n=1 then do; 101 _11=_1; 102 _12=_2; 103 _13=_3; 104 _14=_4; 105 _15=_5; 106 _16=_6; 107 _19=_9; 108 end; 109 if anagr2n=2 then do; 110 _21=_1; 111 _22=_2; 112 _23=_3; 113 _24=_4; 114 _25=_5; 115 _26=_6; 116 _29=_9; 117 end; 118 if anagr2n=3 then do; 119 _31=_1; 120 _32=_2; 121 _33=_3; 122 _34=_4; 123 _35=_5; 124 _36=_6; 125 _39=_9; 126 end; 127 drop _1 _2 _3 _4 _5 _6 _9; 128 run; NOTE: There were 251 observations read from the data set WORK.STEP2. NOTE: The data set WORK.STEP2_ has 251 observations and 28 variables. NOTE: Compressing data set WORK.STEP2_ decreased size by 50.00 percent. Compressed is 2 pages; un-compressed would require 4 pages. NOTE: DATA statement used (Total process time): real time 0.00 seconds user cpu time 0.00 seconds system cpu time 0.00 seconds memory 898.81k OS Memory 67012.00k Timestamp 10/27/2023 06:17:54 PM Step Count 867 Switch Count 4 Page Faults 0 Page Reclaims 62 Page Swaps 0 Voluntary Context Switches 25 Involuntary Context Switches 0 Block Input Operations 0 Block Output Operations 264 129 130 ** Final traspose for ANAGR2N; 131 proc sort data=step2_; by paramcd param avisitn avisit avaln avalcat1 _:; run; NOTE: There were 251 observations read from the data set WORK.STEP2_. NOTE: SAS sort was used. NOTE: The data set WORK.STEP2_ has 251 observations and 28 variables. NOTE: Compressing data set WORK.STEP2_ decreased size by 50.00 percent. Compressed is 2 pages; un-compressed would require 4 pages. NOTE: PROCEDURE SORT used (Total process time): real time 0.00 seconds user cpu time 0.00 seconds system cpu time 0.01 seconds memory 1821.68k OS Memory 68048.00k Timestamp 10/27/2023 06:17:54 PM Step Count 868 Switch Count 4 Page Faults 0 Page Reclaims 123 Page Swaps 0 Voluntary Context Switches 21 Involuntary Context Switches 0 Block Input Operations 0 Block Output Operations 264 132 proc transpose data=step2_(where=(anagr2n ne .)) out=step3(drop=_name_) prefix=_; 133 id anagr2n; 134 by paramcd param avisitn avisit avaln avalcat1 _:; 135 run; NOTE: No variables to transpose. NOTE: There were 251 observations read from the data set WORK.STEP2_. WHERE anagr2n not = .; NOTE: The data set WORK.STEP3 has 0 observations and 30 variables. NOTE: PROCEDURE TRANSPOSE used (Total process time): real time 0.00 seconds user cpu time 0.01 seconds system cpu time 0.00 seconds memory 1036.25k OS Memory 67268.00k Timestamp 10/27/2023 06:17:54 PM Step Count 869 Switch Count 7 Page Faults 0 Page Reclaims 92 Page Swaps 0 Voluntary Context Switches 31 Involuntary Context Switches 0 Block Input Operations 0 Block Output Operations 272 ... View more

Thanks for your reply @maguiremq , I am using below code and it is not working. Am I missing anything?   data _o; set sashelp.vtable; where libname = upcase("OLD"); keep memname; run; proc sort; by memname; run; data _n; set sashelp.vtable; where libname = upcase("NEW"); keep memname; run; proc sort; by memname;run; data both onlyold onlynew; merge _o(in=a) _n(in=b); by memname; if a and b then output both; if a and not b then output onlyold; if b and not a then output onlynew; run; data both; set both onlynew; run; Proc SQL NoPrint; Select Distinct(memname) into :dslist Separated by "|" From Both; Select Distinct(memname) into :onlynew Separated by "|" From OnlyNew; Quit; %put &dslist.; %MACRO test; %DO i = 1 %TO %SYSFUNC(COUNTW(&dslist., %STR(|))); %LET inds = %SCAN(&dslist., &i., %STR(|)); %let varsdrop=StudySiteId siteid site sitenumber folderid StudyEnvSiteNumber RecordDate MinCreated MaxUpdated SaveTS; %macro data_comp(inds=, sort=); proc sort data=old.&inds.(encoding=any) out=old1(drop=&varsdrop.); by _all_; run; proc sort data=new.&inds.(encoding=any) out=new1(drop=&varsdrop.); by _all_; run; proc sort data=old1; by &sort.; run; proc sort data=new1; by &sort.; run; data both_new; merge new1(in=a) old1(in=b keep=&sort.); by &sort.; if a and b; run; data only_new; length flag $50.; merge new1(in=a) old1(in=b keep=&sort.); by &sort.; if a and not b; flag="New"; run; proc compare b=old1 c=both_new out=check1 outnoequal outbase outcomp outdif; run; data check; set check1; where _type_="COMPARE"; run; data updated_new; length flag $50.; merge both_new(in=a) check(in=b keep=&sort.); by &sort.; if a and b then flag="Updated"; run; data nlib.&inds.; set only_new updated_new; run; proc sort; by &sort.; run; %mend; %END; %MEND; %test; Thanks, Adithya ... View more

Hi Patrik,   First of all thank you so much for your reply! I know I am very bad at explaining the things about what I want, sorry for that. But you got it finally. Yes, that is the output I am looking for. But my data is bit challenging. I have 30 datasets in each library to compare and my key variable is record_id and I have lot of variables to compare. Right now, I am using below code, is there any effective way?   %macro data_comp(inds=, sort=); proc sort data=old.&inds. out=old1(drop=&varsdrop.); by _all_; run; proc sort data=db.&inds. out=new1(drop=&varsdrop.); by _all_; run; proc sort data=old1; by &sort.; run; proc sort data=new1; by &sort.; run; data both_new; merge new1(in=a) old1(in=b keep=&sort.); by &sort.; if a and b; run; data only_new; length flag $50.; merge new1(in=a) old1(in=b keep=&sort.); by &sort.; if a and not b; flag="New"; run; proc compare b=old1 c=both_new out=check(where=(_type_="COMPARE")) outnoequal outbase outcomp outdif; run; data updated_new; length flag $50.; merge both_new(in=a) check(in=b keep=&sort.); by &sort.; if a and b then flag="Updated"; run; data nlib.&inds.; set only_new updated_new; run; proc sort; by &sort.; run; %mend; %data_comp(inds=ds1, sort=record_id); Thanks, Adithya     ... View more

Hi,   Sorry for the confusion. Here are the details I am looking for: 1. I need to create a new library with all datasets which are in new transfer. 2. If there are same in datasets in old transfer and new transfer then we need to identify the changes. 3. If there are any changes for the dataset in new transfer such as variable value is updated or a new variable is added then we need to create a new variable with a value of "updated".   Hope this is clear this time 🙂   Thanks, Adithya ... View more

Hi,   Thank you for your reply! Yes, but I am getting all records from both datasets. but I only want records from new transfer. And if any of the variable value is updated compared to old transfer then we need to create a new variable which indicates "updated". I am using below code and how can I compare all variables and to identify which variables is updated?   %macro stack(ds=, drop=); data &ds.1; length source $8 inds $41 ; set old.&ds(in=a) db.&ds(in=b) indsname=inds; if a then flag='ZZZ'; if b then flag='AAA'; source=scan(inds,1,'.'); run; data want; set &ds.1; drop &drop.; run; proc sort nodupkey dupout=chk; by _all_; run; %mend stack; proc sql; create table dslist as select a.memname from dictionary.members a inner join dictionary.members b on a.libname='DB' and b.libname='OLD' and a.memname=b.memname ; quit; %stack(ds=BONE_DENSITY_DXA, drop=source record_id record_type record_status record_status_text date_created date_modified record_deleted); Thanks, Adithya ... View more