Getting missing values for date after proc transpose

[nid197]

Hello everyone,
Could someone please help me with below issue-
My code creates a excel file for 202307 which has 3 columns 1.PERIOD_DT 2.COUNT_LEVEL 3.CNT. Just before the below code I am attaching an image for your reference.And after creating that we are again importing the excel data and creating a sas dataset as
CVM_TRAN_CNT_SUMMARY_0 we are also using data from 1year history excel files and creating the datasets for each month and eventually using it as below given in the code.There are no missing values in excel and the format is mm-dd-yyyy(this is something i checked by selecting the period date column and checking on the date format because the value after opening the excel sheet still display YYYY-MM-DD).I am getting missing values after transpose statement.attaching the result too.


PROC SQL NOPRINT;
CREATE TABLE CM_TRAN_CNT_ SUMMARY_S AS SELECT * FROM CVM_TRAN_CNT_SUMMARY_0 ORDER BY COUNT_LEVEL, PERIOD_ DT;
QUIT;

proc sql;
create table com1. CVM_TRAN_CNT_SUMMARY_S as select distinct PERIOD_DT, COUNT_LEVEL, CNT FROM CV_TRAN_CNT_SUMMARY_S;
quit;

proc sort data=cvm1. CVM_TRAN_CNT_SUMMARY_S; by COUNT_LEVEL;
run;

PROC TRANSPOSE DATA = cvm1. CVM_TRAN_CNT_ SUMMARY_S = CVM_TRAN_CNT_ SUMMARY _T (DROP-_name_ _LABEL_) prefix = M_;
BY COUNT_LEVEL;
ID PERIOD_DT ;
VAR CNT ;
RUN;

data cvm1. CVM_TRAN_CNT_SUMMARY_T ;
set CVM_TRAN_CNT_SUMMARY _T;
run;

PROC SORT DATA=Cvm1.CVM_TRAN_CNT_SUMMARY_S;
BY PERIOD DT; RUN;

[nid197]

Here is another transpose output

[nid197]

Here is the code snippet-

/*Combine all counts*/
%MACRO EXIST_CVM_CNT;
	%if %sysfunc(exist(cvm_account_number_cnt_&alert_month.)) 	%then %do;cvm_account_number_cnt_&alert_month.	%end;
	%if %sysfunc(exist(cvm_transaction_cnt_&alert_month.)) 		%then %do;cvm_transaction_cnt_&alert_month.		%end;
	%if %sysfunc(exist(CVM_TB_CTR_EXMP_TIN_CNT_1&yymm.)) 		%then %do;CVM_TB_CTR_EXMP_TIN_CNT_1&yymm.		%end;
	%if %sysfunc(exist(cvm_cust_cnt_&alert_month.)) 			%then %do;cvm_cust_cnt_&alert_month.			%end;
%MEND;


/*APPEND TOGETHER ALL CUSTOMERS AND ACCOUNTS*/
DATA CVM_CUST_ACCT_TRAN_&Alert_Month.;
	SET %EXIST_CVM_CNT;
RUN;

data cvm1.CUST_ACCT_TRAN_&Alert_Month.;
set CVM_CUST_ACCT_TRAN_&Alert_Month.;
run;

/* =============================================================================*/
/*Export counts to excel file*/
proc export 
  data=CVM_CUST_ACCT_TRAN_&Alert_Month. 
  dbms=xlsx 
  outfile="&CVM_Control./CVM_CUST_ACCT_TRAN_&Alert_Month..xlsx" 
  replace;
run;

%macro FormatMacroVar(value,format);
	%if %datatyp(&value)=CHAR
		%then %sysfunc(putc(&value,&format));
		%else %left(%qsysfunc(putn(&value,&format)));
%mend FormatMacroVar;



%MACRO PULL_CVM_TRAN_CNT(MONTH_CHAR, MONTH_NUM);
	%LET TAG = Cash_Vault_Monitoring_Summary;


PROC IMPORT DATAFILE="&CVM_Control./CVM_CUST_ACCT_TRAN_&MONTH_NUM..xlsx"
		DBMS  = XLSX
		OUT   = CVM_CUST_ACCT_TRAN_&MONTH_NUM.
		REPLACE;
		SHEET = "CVM_CUST_ACCT_TRAN_&MONTH_NUM.";
	RUN;

	PROC SQL;
	CREATE TABLE CVM_TRAN_CNT_&MONTH_NUM. AS
		SELECT 
			&MONTH_NUM. AS  PERIOD_DT,
			COUNT_LEVEL,
			CNT
		FROM CVM_CUST_ACCT_TRAN_&MONTH_NUM.
	QUIT;


%MEND PULL_CVM_TRAN_CNT;

%PULL_CVM_TRAN_CNT(&month0_chr.,&month0.);
%PULL_CVM_TRAN_CNT(&month1_chr.,&month1.);
%PULL_CVM_TRAN_CNT(&month2_chr.,&month2.);
%PULL_CVM_TRAN_CNT(&month3_chr.,&month3.);
%PULL_CVM_TRAN_CNT(&month4_chr.,&month4.);
%PULL_CVM_TRAN_CNT(&month5_chr.,&month5.);
%PULL_CVM_TRAN_CNT(&month6_chr.,&month6.);
%PULL_CVM_TRAN_CNT(&month7_chr.,&month7.);
%PULL_CVM_TRAN_CNT(&month8_chr.,&month8.);
%PULL_CVM_TRAN_CNT(&month9_chr.,&month9.);
%PULL_CVM_TRAN_CNT(&month10_chr.,&month10.);
%PULL_CVM_TRAN_CNT(&month11_chr.,&month11.);
%PULL_CVM_TRAN_CNT(&month12_chr.,&month12.);

%macro ExistCVMCounts();
	%if %sysfunc(exist(CVM_TRAN_CNT_&month0.)) %then %do;CVM_TRAN_CNT_&month0. %end;
	%if %sysfunc(exist(CVM_TRAN_CNT_&month1.)) %then %do;CVM_TRAN_CNT_&month1. %end;
	%if %sysfunc(exist(CVM_TRAN_CNT_&month2.)) %then %do;CVM_TRAN_CNT_&month2. %end;
	%if %sysfunc(exist(CVM_TRAN_CNT_&month3.)) %then %do;CVM_TRAN_CNT_&month3. %end;
	%if %sysfunc(exist(CVM_TRAN_CNT_&month4.)) %then %do;CVM_TRAN_CNT_&month4. %end;
	%if %sysfunc(exist(CVM_TRAN_CNT_&month5.)) %then %do;CVM_TRAN_CNT_&month5. %end;
	%if %sysfunc(exist(CVM_TRAN_CNT_&month6.)) %then %do;CVM_TRAN_CNT_&month6. %end;
	%if %sysfunc(exist(CVM_TRAN_CNT_&month7.)) %then %do;CVM_TRAN_CNT_&month7. %end;
	%if %sysfunc(exist(CVM_TRAN_CNT_&month8.)) %then %do;CVM_TRAN_CNT_&month8. %end;
	%if %sysfunc(exist(CVM_TRAN_CNT_&month9.)) %then %do;CVM_TRAN_CNT_&month9. %end;
	%if %sysfunc(exist(CVM_TRAN_CNT_&month10.)) %then %do;CVM_TRAN_CNT_&month10. %end;
	%if %sysfunc(exist(CVM_TRAN_CNT_&month11.)) %then %do;CVM_TRAN_CNT_&month11. %end;
	%if %sysfunc(exist(CVM_TRAN_CNT_&month12.)) %then %do;CVM_TRAN_CNT_&month12. %end;
%mend ExistCVMCounts;

DATA CVM_TRAN_CNT_SUMMARY_0;
	SET %ExistCVMCounts;
RUN;

data cvm1.cvm_tran_summary_0;
set CVM_TRAN_CNT_SUMMARY_0;
run;

PROC IMPORT 
	DATAFILE="&CVM_Control./CVM_INPUT_PRECHECK_KPIs.xlsx"
	OUT = CVM_INPUT_PRECHECK_KPIs 
	DBMS=xlsx 
	  REPLACE;
	  SHEET="CVM_LIMITS";
	  GETNAMES=YES; 
RUN;



PROC SQL NOPRINT;
	CREATE TABLE cvm1.CVM_TRAN_CNT_SUMMARY_S AS 
		SELECT *
		FROM CVM_TRAN_CNT_SUMMARY_0
		ORDER BY COUNT_LEVEL, PERIOD_DT;
QUIT;

PROC SQL NOPRINT;
	CREATE TABLE CVM_TRAN_CNT_SUMMARY_S AS 
		SELECT *
		FROM CVM_TRAN_CNT_SUMMARY_0
		ORDER BY COUNT_LEVEL, PERIOD_DT;
QUIT;

proc sql;
create table  cvm1.CVM_TRAN_CNT_SUMMARY_S as 
select distinct PERIOD_DT,COUNT_LEVEL,CNT FROM  CVM_TRAN_CNT_SUMMARY_S;
quit;

proc sort data=cvm1.CVM_TRAN_CNT_SUMMARY_S;
by COUNT_LEVEL;
run;

PROC TRANSPOSE DATA = cvm1.CVM_TRAN_CNT_SUMMARY_S 
			   OUT  = CVM_TRAN_CNT_SUMMARY_T(DROP=_name_ _LABEL_)
				prefix = M_;
	BY COUNT_LEVEL;
	ID PERIOD_DT ; 
	VAR  CNT ;
RUN; 

data cvm1.CVM_TRAN_CNT_SUMMARY_T;
set CVM_TRAN_CNT_SUMMARY_T;
run;

PROC SORT DATA=cvm1.CVM_TRAN_CNT_SUMMARY_S; 
	BY PERIOD_DT;
RUN;

PROC TRANSPOSE DATA =cvm1.CVM_TRAN_CNT_SUMMARY_S 
			   OUT  =CVM_TRAN_CNT_SUMMARY_P(DROP=_name_ _LABEL_);
	BY PERIOD_DT;
	ID COUNT_LEVEL ; 
	VAR  CNT ;
RUN; 

[PaigeMiller]

The specific request is to show us "a portion of the actual data in SAS data set CVM_TRAN_CNT_SUMMARY_0, as working SAS data step code (examples and instructions). Do not provide data as screen captures, or copy and paste from Excel or attach files."

The code you provided does not help at all, we need (a portion of) the data in SAS data set CVM_TRAN_CNT_SUMMARY_0.

[nid197]

[Tom]

Results look correct to me.

Since that last dataset has different values of COUNT_LEVEL they are placed into different variable names:

ACCT instead of ACC

EXMP instead of EXM 

TRAN instead of TRA

Since they seem to match on the first three characters you try adding a FORMAT statement to the PROC TRANSPOSE step so that it uses only the first three letters of COUNT_LEVEL to generate the variable names.

format count_level $3.;

[nid197]

Thankyou for your reply.I tried adding format to count_level and will paste the before and after images of the results

Meanwhile could you please check the code again.

/*Count CVM Account Numbers*/
proc sql noprint;
create table cvm_account_number_cnt_&alert_month. as
	SELECT &cvia_extrctdt.  as PERIOD_DT
	,'ACCT' AS COUNT_LEVEL
	,COUNT(Account_Num) AS CNT
	FROM cvm_account_number_&alert_month.
	GROUP BY 1,2
	;
quit;

data cvm1.cvm_acc_number_cnt;
set cvm_account_number_cnt_&alert_month.;
run;


/* =============================================================================*/
/*Count CVM Transactions*/

proc sql noprint;
connect to teradata (server="&mr_td_srv"  user="&mr_usr"  password="&mr_pw" BULKLOAD=no  mode=teradata &QB.);
create table cvm_transaction_cnt_&alert_month. as select * from connection to teradata
	(select &cvia_extrctdt.  as PERIOD_DT
		   ,'TRAN' AS COUNT_LEVEL
			,sum(CASE WHEN Account_Num ne '' THEN 1 ELSE 0 END) as CNT
		   from   &ADWDM_FIU_V1..CVIA_TH
		   group by 1,2
		  where period_dt = &cvia_extrctdt. 
		)%TD_ISSUE;
		disconnect from teradata;
quit;


/* =============================================================================*/
/*Count CTR Exemption records at TIN level*/
proc sql noprint;
connect to teradata (server="&mr_td_srv"  user="&mr_usr"  password="&mr_pw" BULKLOAD=no  mode=teradata &QB.);
create table CVM_TB_CTR_EXMP_1&yymm. as select * from connection to teradata
	(
	SELECT DISTINCT 
    	TIN
    	FROM &P_FCA_AML_U_WORK_03..TB_CTR_EXMP_1&yymm.
	)%TD_ISSUE;								   
disconnect from teradata;
quit;

proc sql noprint;
create table CVM_TB_CTR_EXMP_TIN_CNT_1&yymm. as
	select 
		 &cvia_extrctdt.  as PERIOD_DT
		 ,'EXMP' AS COUNT_LEVEL
	     ,count(*) as CNT
	   from   CVM_TB_CTR_EXMP_1&yymm.
	   group by 1,2
	;
quit;

/* =============================================================================*/
/*Combine all counts*/
%MACRO EXIST_CVM_CNT;
	%if %sysfunc(exist(cvm_account_number_cnt_&alert_month.)) 	%then %do;cvm_account_number_cnt_&alert_month.	%end;
	%if %sysfunc(exist(cvm_transaction_cnt_&alert_month.)) 		%then %do;cvm_transaction_cnt_&alert_month.		%end;
	%if %sysfunc(exist(CVM_TB_CTR_EXMP_TIN_CNT_1&yymm.)) 		%then %do;CVM_TB_CTR_EXMP_TIN_CNT_1&yymm.		%end;
	%if %sysfunc(exist(cvm_cust_cnt_&alert_month.)) 			%then %do;cvm_cust_cnt_&alert_month.			%end;
%MEND;


/*APPEND TOGETHER ALL CUSTOMERS AND ACCOUNTS*/
DATA CVM_CUST_ACCT_TRAN_&Alert_Month.;
	SET %EXIST_CVM_CNT;
RUN;

data cvm1.CUST_ACCT_TRAN_&Alert_Month.;
set CVM_CUST_ACCT_TRAN_&Alert_Month.;
run;

/* =============================================================================*/
/*Export counts to excel file*/
proc export 
  data=CVM_CUST_ACCT_TRAN_&Alert_Month. 
  dbms=xlsx 
  outfile="&CVM_Control./CVM_CUST_ACCT_TRAN_&Alert_Month..xlsx" 
  replace;
run;

%macro FormatMacroVar(value,format);
	%if %datatyp(&value)=CHAR
		%then %sysfunc(putc(&value,&format));
		%else %left(%qsysfunc(putn(&value,&format)));
%mend FormatMacroVar;



%MACRO PULL_CVM_TRAN_CNT(MONTH_CHAR, MONTH_NUM);
	%LET TAG = Cash_Vault_Monitoring_Summary;


PROC IMPORT DATAFILE="&CVM_Control./CVM_CUST_ACCT_TRAN_&MONTH_NUM..xlsx"
		DBMS  = XLSX
		OUT   = CVM_CUST_ACCT_TRAN_&MONTH_NUM.
		REPLACE;
		SHEET = "CVM_CUST_ACCT_TRAN_&MONTH_NUM.";
	RUN;

	PROC SQL;
	CREATE TABLE CVM_TRAN_CNT_&MONTH_NUM. AS
		SELECT 
			&MONTH_NUM. AS  PERIOD_DT,
			COUNT_LEVEL,
			CNT
		FROM CVM_CUST_ACCT_TRAN_&MONTH_NUM.
	QUIT;


%MEND PULL_CVM_TRAN_CNT;

%PULL_CVM_TRAN_CNT(&month0_chr.,&month0.);
%PULL_CVM_TRAN_CNT(&month1_chr.,&month1.);
%PULL_CVM_TRAN_CNT(&month2_chr.,&month2.);
%PULL_CVM_TRAN_CNT(&month3_chr.,&month3.);
%PULL_CVM_TRAN_CNT(&month4_chr.,&month4.);
%PULL_CVM_TRAN_CNT(&month5_chr.,&month5.);
%PULL_CVM_TRAN_CNT(&month6_chr.,&month6.);
%PULL_CVM_TRAN_CNT(&month7_chr.,&month7.);
%PULL_CVM_TRAN_CNT(&month8_chr.,&month8.);
%PULL_CVM_TRAN_CNT(&month9_chr.,&month9.);
%PULL_CVM_TRAN_CNT(&month10_chr.,&month10.);
%PULL_CVM_TRAN_CNT(&month11_chr.,&month11.);
%PULL_CVM_TRAN_CNT(&month12_chr.,&month12.);

%macro ExistCVMCounts();
	%if %sysfunc(exist(CVM_TRAN_CNT_&month0.)) %then %do;CVM_TRAN_CNT_&month0. %end;
	%if %sysfunc(exist(CVM_TRAN_CNT_&month1.)) %then %do;CVM_TRAN_CNT_&month1. %end;
	%if %sysfunc(exist(CVM_TRAN_CNT_&month2.)) %then %do;CVM_TRAN_CNT_&month2. %end;
	%if %sysfunc(exist(CVM_TRAN_CNT_&month3.)) %then %do;CVM_TRAN_CNT_&month3. %end;
	%if %sysfunc(exist(CVM_TRAN_CNT_&month4.)) %then %do;CVM_TRAN_CNT_&month4. %end;
	%if %sysfunc(exist(CVM_TRAN_CNT_&month5.)) %then %do;CVM_TRAN_CNT_&month5. %end;
	%if %sysfunc(exist(CVM_TRAN_CNT_&month6.)) %then %do;CVM_TRAN_CNT_&month6. %end;
	%if %sysfunc(exist(CVM_TRAN_CNT_&month7.)) %then %do;CVM_TRAN_CNT_&month7. %end;
	%if %sysfunc(exist(CVM_TRAN_CNT_&month8.)) %then %do;CVM_TRAN_CNT_&month8. %end;
	%if %sysfunc(exist(CVM_TRAN_CNT_&month9.)) %then %do;CVM_TRAN_CNT_&month9. %end;
	%if %sysfunc(exist(CVM_TRAN_CNT_&month10.)) %then %do;CVM_TRAN_CNT_&month10. %end;
	%if %sysfunc(exist(CVM_TRAN_CNT_&month11.)) %then %do;CVM_TRAN_CNT_&month11. %end;
	%if %sysfunc(exist(CVM_TRAN_CNT_&month12.)) %then %do;CVM_TRAN_CNT_&month12. %end;
%mend ExistCVMCounts;

DATA CVM_TRAN_CNT_SUMMARY_0;
	SET %ExistCVMCounts;
RUN;

data cvm1.cvm_tran_summary_0;
set CVM_TRAN_CNT_SUMMARY_0;
run;

PROC IMPORT 
	DATAFILE="&CVM_Control./CVM_INPUT_PRECHECK_KPIs.xlsx"
	OUT = CVM_INPUT_PRECHECK_KPIs 
	DBMS=xlsx 
	  REPLACE;
	  SHEET="CVM_LIMITS";
	  GETNAMES=YES; 
RUN;



PROC SQL NOPRINT;
	CREATE TABLE cvm1.CVM_TRAN_CNT_SUMMARY_S AS 
		SELECT *
		FROM CVM_TRAN_CNT_SUMMARY_0
		ORDER BY COUNT_LEVEL, PERIOD_DT;
QUIT;

PROC SQL NOPRINT;
	CREATE TABLE CVM_TRAN_CNT_SUMMARY_S AS 
		SELECT *
		FROM CVM_TRAN_CNT_SUMMARY_0
		ORDER BY COUNT_LEVEL, PERIOD_DT;
QUIT;

proc sql;
create table  cvm1.CVM_TRAN_CNT_SUMMARY_S as 
select distinct PERIOD_DT,COUNT_LEVEL,CNT FROM  CVM_TRAN_CNT_SUMMARY_S;
quit;

proc sort data=cvm1.CVM_TRAN_CNT_SUMMARY_S ;
by COUNT_LEVEL;
run;

data cvm1.CVM_TRAN_CNT_SUMMARY_S;
SET cvm1.CVM_TRAN_CNT_SUMMARY_S;
FORMAT COUNT_LEVEL $3.;
RUN;

PROC TRANSPOSE DATA = cvm1.CVM_TRAN_CNT_SUMMARY_S 
			   OUT  = CVM_TRAN_CNT_SUMMARY_T(DROP=_name_ _LABEL_)
				prefix = M_;
	BY COUNT_LEVEL;
	ID PERIOD_DT ; 
	VAR  CNT ;
RUN; 

data cvm1.CVM_TRAN_CNT_SUMMARY_T;
set CVM_TRAN_CNT_SUMMARY_T;
run;


PROC SORT DATA=cvm1.CVM_TRAN_CNT_SUMMARY_S; 
	BY PERIOD_DT;
RUN;

PROC TRANSPOSE DATA =cvm1.CVM_TRAN_CNT_SUMMARY_S 
			   OUT  =CVM_TRAN_CNT_SUMMARY_P(DROP=_name_ _LABEL_);
	BY PERIOD_DT;
	ID COUNT_LEVEL ; 
	VAR  CNT ;
RUN; 

data cvm1.CVM_TRAN_CNT_SUMMARY_P;
set CVM_TRAN_CNT_SUMMARY_P;
run;

PROC SQL NOPRINT;
	CREATE TABLE CVM_TRAN_CNT_SUMMARY_&month0. AS 
		SELECT a.*
				, b.OCLB	
				, b.ICLB	
				, b.MEDIAN	
				, b.ICUB	
				, b.OCUB

		FROM CVM_TRAN_CNT_SUMMARY_T a

		INNER JOIN CVM_INPUT_PRECHECK_KPIs b
			ON b.COUNT_LEVEL = a.COUNT_LEVEL;
QUIT;


ods _all_ close; 
ods excel file = "&CVM_Rephist./CVM_TRAN_CNT_Summary_&month0..xlsx" options(sheet_name="TRAN_CNT_&month0.");
proc report data=CVM_TRAN_CNT_SUMMARY_&month0. nowd;
	 column COUNT_LEVEL M_&MONTH12. M_&MONTH11. M_&MONTH10. M_&MONTH9. M_&MONTH8.	M_&MONTH7.	M_&MONTH6.	M_&MONTH5. M_&MONTH4. M_&MONTH3. M_&MONTH2.	M_&MONTH1. OCLB	ICLB MEDIAN	ICUB OCUB M_&MONTH0.;
	 define COUNT_LEVEL / GROUP;
	 define M_&month12. / display format=comma18.;
	 define M_&month11. / display format=comma18.;
	 define M_&month10. / display format=comma18.;
	 define M_&month9. / display format=comma18.;
	 define M_&month8. / display format=comma18.;
	 define M_&month7. / display format=comma18.;
	 define M_&month6. / display format=comma18.;
	 define M_&month5. / display format=comma18.;
	 define M_&month4. / display format=comma18.;
	 define M_&month3. / display format=comma18.;
	 define M_&month2. / display format=comma18.;
	 define M_&month1. / display format=comma18.;
	 define OCLB	/ display format=comma18.;
	 define ICLB	/display format=comma18.;
	 define MEDIAN	/display format=comma18.;
	 define ICUB	/display format=comma18.;
	 define OCUB	/display format=comma18.;
	 define M_&month0. / display format=comma18.;

	compute M_&month0.;
		if round(M_&month0.) >= round(ICLB) and round(M_&month0.) <= round(ICUB) then call define(_col_,"style","style={background=lightGREEN}");
		else if (round(M_&month0.) >= round(OCLB) and round(M_&month0.) <= round(ICLB)) or (round(M_&month0.) >= round(ICUB) and round(M_&month0.) <= round(OCUB))  then call define(_col_,"style","style={background=yellow}");
		else if round(M_&month0.) < round(OCLB) or round(M_&month0.) > round(OCUB) then call define(_col_,"style","style={background=red}");
		else call define(_col_,"style","style={background=white}");
	endcomp;

run;
ods excel close;
ods listing;

[PaigeMiller]

@nid197 

I suggest to you an alternative approach that is less programming, where macros and macro variables are not needed to get months across the top of your output table. Use long data sets, use PROC REPORT. Do not try to create you own wide data set with months across the top, that is time consuming and requires macros and macro variables. This is consistent with Maxim 19, use long data sets, not wide data sets.

I would provide an example of code that does this with your data, if only you would provide data in a usable form, which is not screen captures, but working SAS data step code, like I requested twice already.

So I provide an example of doing this on a different (long) data set:

proc report data=sashelp.prdsal3(where=(state="California"));
	columns product month,(actual predict);
	define product/group;
	define month/across format=monyy7. order=internal;
	define actual/sum;
	define predict/sum;
run;

See, no macro variables needed to get months across the top of the table. Much less programming required on your part. Easier to maintain and update. Runs faster. No transposing needed because the data is already in the long form that works best for producing this type of output.

I have chopped off months on the right, but they are in the output. Now, what's that, you also want this report by quarters? Piece of cake! Minor change needed. (With macros and macro variables, major changes needed!)

proc report data=sashelp.prdsal3(where=(state="California"));
	columns product month,(actual predict);
	define product/group;
	define month/across format=yyq. order=internal;
	define actual/sum;
	define predict/sum;
run;

I also suggest to you that from now on you describe the final goal, which is an output table with months across the top of the table. Do not simply describe the coding problem where you get stuck. Not describing the final goal and just describing the part of the code where you get stuck is the XY Problem, and it is an inefficient way for you to get the assistance you need. Had we known at the beginning of this thread that you wanted an output table with months across the top, we could get you there a lot faster than what has actually happened in this thread.

[Tom]

That is really too much code to review.

But there are a couple of things that stand out.

Mainly you have not described what the code is actually trying to do.  And we don't have the data (or the files) you are using.  Nor even the values of the macro variables like ALERT_MONTH or MONTH0 that appear in the middle of the code without first being assigned any values. 

The code appears to be using PROC IMPORT to get DATA from an EXCEL worksheet.  That will mean that the lengths (and even the types and names) of the variables could change with every edit of the EXCEL worksheet.

Also watch out for doing something like this in TERADATA code.

sum(CASE WHEN Account_Num ne '' THEN 1 ELSE 0 END) as CNT

TERADATA will see the constants 1 and 0 as SMALL INTEGER value and so make CNT as a SMALL INTEGER variable.  So you might get value overflows for large counts.  And large counts is the main reason to have data in TERADATA any way.

And speaking of TERADATA do you really have the data for multiple months in separate tables in the TERADATA database? Why?  The whole point of using TERADATA is you can store terabytes of data.  And if not then why not just ask TERADATA to do the heavy lifting and have it generate the counts for all of the months in one query and then just pull that small result set to SAS to finish formatting your report?

Also you included this macro (but do not seem to have used it anywhere) which has the test BACKWARDS.

%macro FormatMacroVar(value,format);
%if %datatyp(&value)=CHAR %then %sysfunc(putc(&value,&format));
%else %left(%qsysfunc(putn(&value,&format)));
%mend FormatMacroVar;

The test to see whether you want to call PUTN() or PUTC() should be based on the FORMAT, not the value being formatted.  That is a much more reliable test then depending on the macro DATTYPE to guess if the value is a number or a string.  What is the right answer for an ICD9 code?

Just test if the first character of the format specification is a $ or not.  And why remove the leading spaces from the formatted numeric values and not the formatted character values?

%macro FormatMacroVar(value,format);
%if %qsubstr(&format,1,1)=$ %then %left(%qsysfunc(putc(&value,&format)));
%else %left(%qsysfunc(putn(&value,&format)));
%mend FormatMacroVar;

[PaigeMiller]

It would be helpful if you could provide a portion of the actual data in SAS data set CVM_TRAN_CNT_SUMMARY_0, as working SAS data step code (examples and instructions). Do not provide data as screen captures, or copy and paste from Excel or attach files.

Also, I never download file attachments so I can't see your PDF files. Please include any screen captures of the output by using the "Insert Photos" icon.

[yabwon]

You wrote "There are no missing values in excel", what about SAS data sets after importing the excel?

Bart

[nid197]

There were no missing values after importing the excel.I see missing values only after transpose. As you can see in the newly attached code, imported and sorted table is cvm_tran_cnt_summary_s

[yabwon]

One more thing, beside the fact that in the code snippet you shared there are some "unexpected" spaces, the libraries names are quite inconsistent:

are you sure you are using proper file?

One more thing, does your data have all values? Maybe you have missing data because you simply have no data?

Bart

[nid197]

just posted the correct code.Could you please check

[nid197]

Hi,Thankyou for your reply.I have shared the correct sas code now.Could you please check once and advice.