Replacing DDE with Proc Export

Reply
Occasional Contributor
Posts: 16

Replacing DDE with Proc Export

Data Blank;

  format Date monyy7.;

  do i = 1940 to year("01&thisperiod"d) by 1;

  do j = 6 to 12 by 6;

  Date = MDY(j,1,i);

  output;

  end;

  end;

  keep Date;

run;

*

%macro guts(stat);

%if "&stat" ^= "rawdata" %then %do;

  Proc univariate data=Live.STUFF Noprint;

  by type date;

  where Type = "&Type";

  var BasePeriod Forecast0Month Forecast6Month Forecast12Month

  BaseYear Forecast0Year Forecast1Year Forecast2Year Forecast10Year;

  output out=&stat._file

  &stat=BasePeriod Forecast0Month Forecast6Month Forecast12Month

  BaseYear Forecast0Year Forecast1Year Forecast2Year Forecast10Year;

  run;

  Data &stat._file;

  merge &stat._file (in=a) Blank;

  by Date;

  k+0;

  if Type = "&Type" then k = 1;

  if a = 0 and k = 1 then Type = "&Type";

  run;

%end;

Data _NULL_;

    file &stat;

    %if "&stat" = "rawdata" %then %do;set Live.STUFF;%end;

  %else %do;set &stat._file;%end;

  if Type = "&Type" then do;

  %if "&stat" = "rawdata" %then %do;put Date ID Category @;%end;

  %else %do;put Date @;%end;

  %if "&Type" = "RGDPX" or "&Type" = "CPI" %then %do;

    array out [9] BasePeriod Forecast0Month Forecast6Month Forecast12Month

   BaseYear Forecast0Year Forecast1Year Forecast2Year Forecast10Year;

  %end;

  %else %do;

  array out [8] BasePeriod Forecast0Month Forecast6Month Forecast12Month

   BaseYear Forecast0Year Forecast1Year Forecast2Year;

  %end;

  do i = 1 to dim(out);

  if out ^= . then put out @;

  else put '=na() ' @;

  end;put;

  end;

Run;

%mend guts;

%macro export(type);

Currently, the part in blue works perfectly fine with the program. I'm trying to convert FROM DDE TO PROC EXPORT, and I'm replacing the BLUE(DDE) with RED(PROC EXPORT), however, the macros don't resolve..any suggestions?


filename mean dde "excel|C:\[means.xls]&Type!r2c1:r300c10";

filename median dde "excel|C:\[medians.xls]&Type!r2c1:r300c10";

filename rawdata dde "excel|C:\[rawdata.xls]&Type!r2c1:r7000c12";

proc export data = &stat._file

outfile="&meansfile"

dbms=excel

replace;

sheet=&type;

run;

proc export data = &stat._file

outfile="&mediansfile"

dbms=excel

replace;

sheet=&type;

run;

proc export data = &stat

outfile="&rawdatafile"

dbms=excel

replace;

sheet=&type;

run;

%guts(mean)

%guts(median)

%guts(rawdata)

%mend export;

%export(type=RGDPX)

%export(type=GDPX)

%export(type=BFIX)

%export(type=CPAT)

%export(type=IP)

%export(type=TPHS)

%export(type=PPI)

%export(type=CPI)

%export(type=UNPR)

%export(type=WMFG)

%export(type=RTTR)

%export(type=AUTODF)

%export(type=PRIME)

%export(type=TBOND)

%export(type=TBILL)

%export(type=SPIF)

Super Contributor
Posts: 644

Re: Replacing DDE with Proc Export

You might need to put

sheet="&type";


Richard

Occasional Contributor
Posts: 16

Re: Replacing DDE with Proc Export

I just tried it..Still didn't work...Here is what the first couple of lines of errors(RED) and warnings(GREEN) on the log says

%export(type=RGDPX)

MLOGIC(EXPORT):  Beginning execution.

MLOGIC(EXPORT):  Parameter TYPE has value RGDPX

NOTE: Line generated by the invoked macro "EXPORT".

1      proc export data = &stat._file outfile="&meansfile" dbms=excel replace; sheet="&type";

                                   -

                                   22

                                   200

1  ! run; proc export data = &stat._file outfile="&mediansfile" dbms=excel replace;

1  ! sheet="&type"; run; proc export data = &stat outfile="&rawdatafile" dbms=excel replace;

WARNING: Apparent symbolic reference STAT not resolved.

SYMBOLGEN:  Macro variable MEANSFILE resolves to H:\SAS Export\meanfilestuff

NOTE: PROCEDURE EXPORT used (Total process time):

      real time           0.00 seconds

      cpu time            0.00 seconds

MPRINT(EXPORT):   proc export data = &stat._file outfile="H:\SAS Export\meanfilestuff"

dbms=excel replace;

NOTE: The SAS System stopped processing this step because of errors.

SYMBOLGEN:  Macro variable TYPE resolves to RGDPX

MPRINT(EXPORT):   sheet="RGDPX";

MPRINT(EXPORT):   run;

ERROR 22-322: Expecting a name.

ERROR 200-322: The symbol is not recognized and will be ignored.

22: LINE and COLUMN cannot be determined.

NOTE 242-205: NOSPOOL is on. Rerunning with OPTION SPOOL might allow recovery of the LINE and

              COLUMN where the error has occurred.

ERROR 22-322: Expecting a name.

200: LINE and COLUMN cannot be determined.

NOTE: NOSPOOL is on. Rerunning with OPTION SPOOL might allow recovery of the LINE and COLUMN

      where the error has occurred.

ERROR 200-322: The symbol is not recognized and will be ignored.

WARNING: Apparent symbolic reference STAT not resolved.

Super User
Posts: 10,538

Re: Replacing DDE with Proc Export

Your macro variable STAT only exists while the macro GUTS is running. It is not defined in your Export macro.

Occasional Contributor
Posts: 16

Re: Replacing DDE with Proc Export

Any suggestions on resolve that issue?

Super User
Posts: 17,912

Re: Replacing DDE with Proc Export

Change the scope to global. Google MACRO SCOPE SAS

Ask a Question
Discussion stats
  • 5 replies
  • 329 views
  • 0 likes
  • 4 in conversation