Desktop productivity for business analysts and programmers

Advanced do loop

Accepted Solution Solved
Reply
Contributor
Posts: 45
Accepted Solution

Advanced do loop

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;

 


Accepted Solutions
Solution
‎09-01-2016 09:57 AM
Respected Advisor
Posts: 4,992

Re: Advanced do loop

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

View solution in original post


All Replies
Grand Advisor
Posts: 17,396

Re: Advanced do loop

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

Contributor
Posts: 45

Re: Advanced do loop

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.

Respected Advisor
Posts: 4,992

Re: Advanced do loop

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?

Contributor
Posts: 45

Re: Advanced do loop

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. 

Grand Advisor
Posts: 17,396

Re: Advanced do loop

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

Solution
‎09-01-2016 09:57 AM
Respected Advisor
Posts: 4,992

Re: Advanced do loop

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

Contributor
Posts: 45

Re: Advanced do loop

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

Respected Advisor
Posts: 4,992

Re: Advanced do loop

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

Contributor
Posts: 45

Re: Advanced do loop

sorry, i was looking at the wrong data _null_ 

 

Yay! it works now!

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 9 replies
  • 458 views
  • 1 like
  • 3 in conversation