09-17-2013 08:42 AM
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;
filename FAQ_DDE dde 'excel|sheet1!R1C1:R1C2';
%LET YR = 2007;
%LET MTH = 01;
%LET PROD = 'Autos';
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.;
PUT (_ALL_) ('09'x);
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.
09-17-2013 09:00 AM
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.
09-17-2013 10:22 AM
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.
Need further help from the community? Please ask a new question.