topic Re: Question 1: Using SAS code (proc sql, data steps, any other functions and procedures), create a in SAS Programming

Question 1: Using SAS code (proc sql, data steps, any other functions and procedures), create a data

NITYA0411 — Sun, 28 Mar 2021 18:34:46 GMT

                                                                                                                                                                                                                                                 
DATA ACCT;                                                                                                                                                                                                                                         
INPUT ACCOUNT_ID ACCOUNT_TYPE$ BALANCE$ BALANCEDATE DATE9.;                                                                                                                                                                                        
FORMAT BALANCEDATE DATE9.;                                                                                                                                                                                                                         
DATALINES;                                                                                                                                                                                                                                         
123 CHEQUING $234 30-NOV-20                                                                                                                                                                                                                        
234 CHEQUING $100 30-NOV-20                                                                                                                                                                                                                        
321 SAVING $150 30-NOV-20                                                                                                                                                                                                                          
456 CHEQUING $10,000 30-NOV-20                                                                                                                                                                                                                     
678 CARD $435 30-NOV-20                                                                                                                                                                                                                            
789 INVESTMENT $999 31-OCT-20                                                                                                                                                                                                                      
123 CHEQUING $155 31-OCT-20                                                                                                                                                                                                                        
234 CHEQUING $67 31-OCT-20                                                                                                                                                                                                                         
321 SAVING $0 31-OCT-20                                                                                                                                                                                                                            
456 CHEQUING $1,000 31-OCT-20                                                                                                                                                                                                                      
234 CHEQUING $500 30-SEP-20                                                                                                                                                                                                                        
321 SAVING $250 30-SEP-20                                                                                                                                                                                                                          
;                                                                                                                                                                                                                                                  
RUN;                                                                                                                                                                                                                                               
                                                                                                                                                                                                                                                   
                                                                                                                                                                                                                                                   
                                                                                                                                                                                                                                                   
                                                                                                                                                                                                                                                   
DATA ACCTXREF;                                                                                                                                                                                                                                     
INPUT ACCOUNT_ID CUSTOMER_ID ACCOUNT_MONTH DATE9.;                                                                                                                                                                                                 
FORMAT ACCOUNT_MONTH DATE9.;                                                                                                                                                                                                                       
DATALINES;                                                                                                                                                                                                                                         
123 3010 30-NOV-20                                                                                                                                                                                                                                 
234 3010 30-NOV-20                                                                                                                                                                                                                                 
234 8788 30-NOV-20                                                                                                                                                                                                                                 
321 4556 30-NOV-20                                                                                                                                                                                                                                 
456 6789 30-NOV-20                                                                                                                                                                                                                                 
678 4510 30-NOV-20                                                                                                                                                                                                                                 
789 3319 30-NOV-20                                                                                                                                                                                                                                 
123 3010 31-OCT-20                                                                                                                                                                                                                                 
234 3010 31-OCT-20                                                                                                                                                                                                                                 
234 8788 31-OCT-20                                                                                                                                                                                                                                 
321 4556 31-OCT-20                                                                                                                                                                                                                                 
456 6789 31-OCT-20                                                                                                                                                                                                                                 
234 3010 30-SEP-20                                                                                                                                                                                                                                 
234 8788 30-SEP-20                                                                                                                                                                                                                                 
321 4556 30-SEP-20                                                                                                                                                                                                                                 
;                                                                                                                                                                                                                                                  
RUN;                                                                                                                                                                                                                                               
                                                                                                                                                                                                                                                   
                                                                                                                                                                                                                                                   
                                                                                                                                                                                                                                                   
                                                                                                                                                                                                                                                   
                                                                                                                                                                                                                                                   
PROC SORT DATA=ACCT;                                                                                                                                                                                                                               
BY ACCOUNT_ID;                                                                                                                                                                                                                                     
RUN;                                                                                                                                                                                                                                               
                                                                                                                                                                                                                                                   
PROC SORT DATA=ACCTXREF;                                                                                                                                                                                                                           
BY ACCOUNT_ID;                                                                                                                                                                                                                                     
RUN;                                                                                                                                                                                                                                               
                                                                                                                                                                                                                                                   
DATA MERG;                                                                                                                                                                                                                                         
MERGE ACCT ACCTXREF(RENAME=(ACCOUNT_MONTH=BALANCEDATE));                                                                                                                                                                                           
BY ACCOUNT_ID;                                                                                                                                                                                                                                     
RUN;                                                                                                                                                                                                                                               
                                                                                                                                                                                                                                                   
 DATA TASK1;                                                                                                                                                                                                                                       
SET MERG;                                                                                                                                                                                                                                          
WHERE ACCOUNT_TYPE IN("CHEQUING","SAVING");                                                                                                                                                                                                        
WHERE ALSO BALANCEDATE='30NOV-20'D;                                                                                                                                                                                                                
RUN;                                                                                                                                                                                                                                               
                                                                                                                                                                                                                                                   
PROC SORT DATA=TASK1;                                                                                                                                                                                                                              
BY CUSTOMER_ID;                                                                                                                                                                                                                                    
RUN;                                                                                                                                                                                                                                               
                                                                                                                                                                                                                                                   
DATA TASK_1;                                                                                                                                                                                                                                       
SET TASK1;                                                                                                                                                                                                                                         
BY CUSTOMER_ID;                                                                                                                                                                                                                                    
IF FIRST.CUSTOMER_ID=1 THEN TOTAL_BALANCE=BALANCE;                                                                                                                                                                                                 
ELSE TOTAL_BALANCE=BALANCE+TOTAL_BALANCE;                                                                                                                                                                                                          
RUN;

Re: Question 1: Using SAS code (proc sql, data steps, any other functions and procedures), create a

NITYA0411 — Sun, 28 Mar 2021 18:35:46 GMT

Question 1: Using SAS code (proc sql, data steps, any other functions and procedures), create a dataset showing the total balance in chequing and savings accounts for each customer on November 30, 2020.

THIS IS THE QUESTION

Re: Question 1: Using SAS code (proc sql, data steps, any other functions and procedures), create a

NITYA0411 — Sun, 28 Mar 2021 18:36:40 GMT

DONT KNOW WHICH DATE FORMAT WIL BE USED, AND HOW TO GET THE TOTAL FOR EACH CUSTOMER

Re: Question 1: Using SAS code (proc sql, data steps, any other functions and procedures), create a

ballardw — Mon, 29 Mar 2021 14:23:21 GMT

DON'T YELL. All capital letters is yelling.

First thing, your date values are NOT in date9. format, date9. is ddMONyyyy, 4 digit year and no dashes.

I would suggest using 4 digit years in anything but there are people that did not spend hours fixing year 2000 in data set that failed to learn the lesson.

Why are you merging those data sets when there is not a stated requirement?

Proc summary or means with CLASS or by statement will sum variables. I would say your real question is how to just get the specific types of accounts.

Proc summary data=acct nway;
   class account_id balancedate;
   where account_type in ('CHEQUING' 'SAVING');
   var balance;
   output out=want sum=;
run;

will sum the value of balance for the types of accounts in the Where statement for each combination of account_id and balancedate.

You could add a specific value or range of values for balancedate to the Where statment. Left as an exercise so you learn something at this point.

Re: Question 1: Using SAS code (proc sql, data steps, any other functions and procedures), create a

NITYA0411 — Sun, 28 Mar 2021 22:13:37 GMT

HI,

Thank you for your response. I really appreciate it.
Tried using proc summary, but showing this error.
ERROR: Variable BALANCE in list does not match type prescribed for this
list.

Re: Question 1: Using SAS code (proc sql, data steps, any other functions and procedures), create a

ChrisNZ — Mon, 29 Mar 2021 02:02:41 GMT

input ACCOUNT_ID ACCOUNT_TYPE $ BALANCE $ BALANCEDATE date9.;

tells SAS that BALANCE is a string.

So you need to fix this informat as well (as in: on top of fixing the date informat).