Advanced do loop

[SVoldrich]

SAS Enterprise Guide 6.1

I have a crosswalk table
legacysystem  legacytable  legacycolumn  newsystem  newtable     newcolumn

appdm             drg               drg_cd            APSYS          REF_DRG  DRG_CD

appdm             drg               drg_desc        APSYS           REF_DRG  DRG_DESC

etc.

There are 5 fields in teh APPDM.DRG table.

I need to perform analysis to make sure all the values in APPDM are in the new system.

The Loop is working, in that it repeats this 5 times - but it does the same action 5 times for the same field. 

%let mapping=Crosswalk_Mapping_APPDM_DRG;
%let LegSys=APPDM;
%let LegTab=DRG;
%let APSYSSys=APSYSVIEW;

proc sql; describe table &mapping; quit;

proc sql;
select count (distinct Legacy_Source_Column) as CNT_LEGACY
from &mapping;
quit;

proc sort data=&mapping;
by Legacy_Source_Column;
run;

data WithCount;
set &mapping;
count+1;
by Legacy_Source_Column;
run;

DATA _NULL_;
SET WithCount END=END;
IF END THEN
DO;
CALL SYMPUT ('TOT_OBS', _N_);
END;
RUN;

%PUT &TOT_OBS;

%MACRO DO_IT;

DATA WORK.THIS_ONE;
SET WithCount;

IF &I = _N_ THEN
OUTPUT;
RUN;

DATA _NULL_;
SET WithCount;
CALL SYMPUT ('Legacy_Source_Column',TRIM(Legacy_Source_Column));
CALL SYMPUT ('APSYS_Source_Database',TRIM(APSYS_Source_Database));
CALL SYMPUT ('APSYS_Source_Table',TRIM(APSYS_Source_Table));
CALL SYMPUT ('APSYS_Source_Column',TRIM(APSYS_Source_Column));
RUN;

%let Legacysystem=&LegSys;
%let LegacyTable=&LegTab;
%let LegacyColumn=&Legacy_Source_Column;

proc sql;
create table Legacy as
select distinct
&LegacyColumn as Field
from &Legacysystem..&LegacyTable
order by Field;
quit;

%let APSYSsystem=&APSYSSys;
%let APSYSTable=&APSYS_Source_Table;
%let APSYSColumn=&APSYS_Source_Column;

proc sql;

create table APSYS as
select distinct
&APSYSColumn as Field
from &APSYSsystem..&APSYSTable
order by Field;
quit;

data compare_legacy_&LegacyColumn;
merge Legacy (in=a) APSYS (in=b);
by Field;
if a and b then status = 'Both ';
if a and not b then status ='in legacy only';
if b and not a then status ='in APSYS only';
Run;

proc freq data =compare_Legacy_&LegacyColumn;
title'Compare Data Elements between Legacy and APSYS '&LegacyColumn;
table status/missing;
run;

%MEND DO_IT;

%MACRO LOOP;
%DO I=1 %TO &TOT_OBS;
%PUT "---------------------------------------------";
%PUT &TOT_OBS LOOPS -> &I;
%PUT "---------------------------------------------";

%DO_IT;
RUN;

%END;
%MEND LOOP;

%LOOP;

[Reeza]

I didn't trace it all but your data null step, should that refer to table from previous step instead of current assigned dataset?

[SVoldrich]

I think this is probably where the issue iies.

some how it's not counting in such a way to know to go to the next one.

But i'm not sure i am following when you say previous step vs current assigned dataset.

[Astounding]

Your data set WithCount is essentially the same as your incoming data set &mapping (with COUNT added).  There is nothing about it that summarizes the records in any way.  Is that your intention?

[SVoldrich]

Not sure that i follow....
This one tells me there are 5 rows in this table.

proc sql;
select count (distinct Legacy_Source_Column) as CNT_LEGACY
from &mapping;
quit;

This one lists the count at the end of the table. 

data WithCount;
set &mapping;
count+1;
by Legacy_Source_Column;
run;

My issue is that it is not identifying the different iterations and moving down the list. 

[Reeza]

You should also move your %DO loop to inside your macro to have a single macro. 

[Astounding · Accepted solution]

Reeza's original suggestion looks correct.  This step refers to the wrong data set:

data _null_;

set withCount;

CALL SYMPUT ...

It should use:

data _null_;

set ThisOne;

CALL SYMPUT ...

[SVoldrich]

ok, but then it needs to be put somewhere else in order because ThisOne isn't created until after that data _null_ query.

[Astounding]

No, it should be OK.  In your posted code, ThisOne is created immediately before the DATA _NULL_ step.

[SVoldrich]

sorry, i was looking at the wrong data _null_ 

Yay! it works now!