EFFICIENT WAY OF USING DDE WITH A LOOP?

Accepted Solution Solved
Reply
Contributor
Posts: 51
Accepted Solution

EFFICIENT WAY OF USING DDE WITH A LOOP?

Hi friends,

While my following code is working good enough to extract data pertaining to a Month of a special Year on a specific Excel Sheet's cell:

options noxwait noxsync;

x '"F:\eLab\SAS_Experiments\MySAS.xlsx"';

filename FAQ_DDE dde 'excel|sheet1!R1C1:R1C2';

%LET YR = 2007;

%LET MTH = 01;

%LET PROD = 'Autos';

PROC SQL;

CREATE TABLE Work.ABC as SELECT POS FROM (SELECT put(DPD, bucket.) as DPD_Bucket, PRODUCT, LOAN_STATUS, count(Customer_Name) AS Case_Count, sum(POS) AS POS from FAQ.Data_&YR._&MTH. GROUP by PRODUCT, LOAN_STATUS, DPD_Bucket) WHERE PRODUCT = &PROD.;

Quit;

data _null_;

set ABC;

FILE FAQ_DDE;

PUT (_ALL_) ('09'x);

run;

sure would oblige if any of you experts could recommend to me a way to use a LOOP so as to have data extracted in shape of a table with months in rows and years in columns. In other words having my own code being changed to a macro and the same being called as to have the Variables YR and MTH spread over 2007 to 2012 and 1 to 12, respectively.

Furthermore, the existing code also has the first column left blank in Excel. Any reason and way to resolve the same?

Thanx in advance.


Accepted Solutions
Solution
‎09-17-2013 10:25 AM
Super User
Posts: 17,961

Re: EFFICIENT WAY OF USING DDE WITH A LOOP?

Base SAS: Demo: ExcelXP Tagset and Microsoft Excel

See the section titled "Manual Multiple Worksheets" for a tabulate example.

View solution in original post


All Replies
Super User
Posts: 5,260

Re: EFFICIENT WAY OF USING DDE WITH A LOOP?

Any particular reason for using DDE? It's kind of old fashioned, and Microsoft isn't really supporting that any more.

looping: build a macro that %do loop over month as inner loop, and year in the outer loop. Have the SQL insert to a common target table. Then at the end use a reporting procedure to do the lay-out, such as TABULATE. The result can be displayed in Excel using ODS tagsets.

Data never sleeps
Contributor
Posts: 51

Re: EFFICIENT WAY OF USING DDE WITH A LOOP?

Thanx for disclosing the fact of DDE's mode outdating, LinusH. However, with no former experience please see if you can provide a sample code in this case (or even any of your own example) to use the ODS and TABULATE.

Solution
‎09-17-2013 10:25 AM
Super User
Posts: 17,961

Re: EFFICIENT WAY OF USING DDE WITH A LOOP?

Base SAS: Demo: ExcelXP Tagset and Microsoft Excel

See the section titled "Manual Multiple Worksheets" for a tabulate example.

Contributor
Posts: 51

Re: EFFICIENT WAY OF USING DDE WITH A LOOP?

Thanx Reeza!
Sure did find the source referred, to be an excellent recommendation!

🔒 This topic is solved and locked.

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

Discussion stats
  • 4 replies
  • 227 views
  • 3 likes
  • 3 in conversation