topic Re: Advanced do loop in SAS Enterprise Guide

Advanced do loop

SVoldrich — Wed, 31 Aug 2016 23:23:49 GMT

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;

Re: Advanced do loop

Reeza — Thu, 01 Sep 2016 01:39:25 GMT

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

Re: Advanced do loop

Astounding — Thu, 01 Sep 2016 02:21:03 GMT

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?

Re: Advanced do loop

Reeza — Thu, 01 Sep 2016 08:49:25 GMT

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

Re: Advanced do loop

SVoldrich — Thu, 01 Sep 2016 11:02:21 GMT

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.

Re: Advanced do loop

SVoldrich — Thu, 01 Sep 2016 11:08:25 GMT

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.

Re: Advanced do loop

Astounding — Thu, 01 Sep 2016 13:09:39 GMT

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 ...

Re: Advanced do loop

SVoldrich — Thu, 01 Sep 2016 13:24:13 GMT

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

Re: Advanced do loop

Astounding — Thu, 01 Sep 2016 13:31:43 GMT

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

Re: Advanced do loop

SVoldrich — Thu, 01 Sep 2016 13:56:53 GMT

sorry, i was looking at the wrong data _null_

Yay! it works now!