This post was not spam how do I I get it back up? I have a weekly report I am running where I need to take an unknown number of columns that I need to convert to a running total. Please try to keep in mind I created this very simple example, the data including the headers are much more complicated, Also I am trying to automate this so I never would have to actually look at the the file to run my program. Variable names will never be the same from day to day Below is the code I have started with please let me know what I am doing wrong, Macros are not my specialty so I am very open if I am going the wrong way about this. DATA HAVE;
infile datalines delimiter=',';
INPUT
SALES_ID $ MONDAY TUESDAY WEDNESDAY THURSDAY FRIDAY SATURDAY;
DATALINES;
A1,45,61,60,14,74,35
A2,65,72,68,79,70,30
A3,24,17,16,69,29,18
A4,81,84,46,82,49,34
A5,81,38,67,40,88,11
A6,52,89,47,48,68,70
A7,62,10,41,73,49,42
;
RUN;
PROC CONTENTS DATA = have OUT = COL_STEP1;
RUN;
/*The varnum greater than one will always be know, however the total number of columns will can not be hard coded*/
PROC SQL;
CREATE TABLE COLS AS
SELECT DISTINCT
NAME AS COL,
VARNUM AS COL_NUM
FROM COL_STEP1
WHERE VARNUM > 1
ORDER BY VARNUM;
QUIT;
PROC SQL NOPRINT;
SELECT DISTINCT MAX(COL_NUM)-2 INTO :N
FROM COLS;
QUIT;
PROC SQL NOPRINT;
SELECT DISTINCT col INTO :col1
FROM COLS
having col_num = min(col_num);
QUIT;
DATA IFILE (KEEP = COL);
SET COLS;
WHERE COL_NUM > 1;
IF &N = 1 THEN STOP;
RUN;
%PUT NOTE: &N columns to aggregate &col1;
PROC SQL;
CREATE TABLE ID_TABLE AS
SELECT
SALES_ID,
&COL1,
&COL1 AS TOTAL
FROM HAVE;
QUIT;
%MACRO TEST;
%DO X = 1 %TO &N;
DATA _NULL_;
SET IFILE (FIRSTOBS=&X OBS=&X);
CALL SYMPUT ('COL', STRIP(COL));
RUN;
PROC SQL;
CREATE TABLE ID_TABLE AS
SELECT A.SALES_ID,
B.&COL + A.TOTAL AS &COL,
B.&COL + A.TOTAL AS TOTAL
FROM ID_TABLE AS A
LEFT JOIN HAVE AS B
ON A.SALES_ID = B.SALES_ID;
QUIT;
%END;
%MEND TEST;
%TEST;
... View more
