cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
gabrielstraliot
‎08-10-2022
  • 2 Posts
  • 1 Likes Given
  • 0 Solutions
  • 0 Likes Received

Re: Filtering using DATA STEP from another table

by Calcite | Level 5 in SAS Enterprise Guide
‎08-08-2022 01:25 PM
‎08-08-2022 01:25 PM
I simulated the data to better exemplify:   DATA WORK.Transactions; INFILE DATALINES dlm = ","; INPUT ACCOUNT $ TYPE $ VALUE DATE :ddmmyy10.; format DATE :ddmmyy10.; DATALINES; 00001-1,C,20,14/06/2022 00002-2,D,1,20/06/2022 00003-3,D,28,16/05/2022 00001-2,D,100,04/07/2022 00002-3,D,50,11/07/2022 00003-4,C,50,27/06/2022 00001-3,C,30,18/07/2022 00001-1,D,500,09/05/2022 00002-2,C,10000,14/06/2022 00003-3,D,10000,01/08/2022 00001-2,C,10,16/05/2022 00002-3,D,12,14/06/2022 00003-4,C,90,06/06/2022 00001-3,D,2,04/07/2022 00001-1,D,20,14/06/2022 00002-2,D,1,18/07/2022 00003-3,C,28,14/06/2022 00001-2,D,100,09/05/2022 00002-3,C,50,11/07/2022 00003-4,D,50,18/07/2022 00001-3,C,30,02/05/2022 00001-1,D,500,25/07/2022 00002-2,C,10000,30/05/2022 00003-3,C,10000,25/07/2022 00001-2,C,10,01/08/2022 00002-3,D,12,14/06/2022 00003-4,C,90,25/07/2022 00001-3,D,2,30/05/2022 ;RUN;   DATA WORK.ACCOUNTS; INFILE DATALINES dlm = ","; INPUT NAME $ ACCOUNT $ MONTH :ddmmyy10. SELECT $; format MONTH MMYYS.; DATALINES; Jacoby Brook,00001-1,01/06/22,Y Sample Billy,00002-2,01/06/22,N Bando Chris,00003-3,01/06/22,N Castillo Carmen,00001-2,01/06/22,Y Hall Mel,00003-4,01/06/22,N Barrett Marty,00002-3,01/07/22,N Hall Mel,00003-4,01/07/22,Y Moreno Omar,00001-3,01/07/22,Y Jacoby Brook,00001-1,01/07/22,N ;RUN;   This is an example of how I do it. I need to separate transactions. Thanks for the LEFT JOIN suggestion. But can I do it by DATA STEP to perform better ?   %LET DATAI = %SYSFUNC(INTNX(MONTH, %SYSFUNC(TODAY()), -1, B), DATE9.); %LET DATAF = %SYSFUNC(INTNX(MONTH, %SYSFUNC(TODAY()), -1, E), DATE9.); PROC SQL; CREATE TABLE WORK.FINAL_LIST_SQL AS SELECT t1.ACCOUNT, t2.NAME, t1.TYPE, t1.VALUE, t1.DATE FROM WORK.TRANSACTIONS t1 LEFT JOIN WORK.ACCOUNTS T2 ON t1.ACCOUNT = t2.ACCOUNT WHERE T1.DATE BETWEEN "&DATAI."D AND "&DATAF."D AND T2.SELECT = 'Y' AND T2.MONTH BETWEEN "&DATAI."D AND "&DATAF."D ;QUIT;     ... View more

Filtering using DATA STEP from another table

by Calcite | Level 5 in SAS Enterprise Guide
‎08-08-2022 09:44 AM
‎08-08-2022 09:44 AM
Hello, i need to do a DATA STEP filtering data from another table. This example table contains the variables that I use for the filter: DATA WORK.TEAMS; SET SASHELP.BASEBALL (KEEP=TEAM WHERE=(TEAM IN ('Cleveland', 'Atlanta', 'Boston'))); RUN; I'm currently using select sql, however, it doesn't perform well. I want to use data step for this. PROC SQL; CREATE TABLE WORK.LIST_SQL AS SELECT t1.Name, t1.Team, t1.League, t1.Division, t1.Position FROM SASHELP.BASEBALL T1 INNER JOIN WORK.TEAMS T2 ON T1.TEAM = T2.TEAM ;QUIT; I can't use a variable like this below because the values i have ​​are dynamic %LET TEAM = 'Cleveland', 'Atlanta', 'Boston'; DATA WORK.LIST_SAS; SET SASHELP.BASEBALL (WHERE=(TEAM IN (&TEAM.))); RUN;   For context, I want to filter financial transactions from codes, however, these codes vary from month to month.   I can transform the data of this table (TEAMS) into a variable? what is the best alternative? ... View more
Contact Me
Online Status
Offline
Date Last Visited
‎08-10-2022 12:04 PM
Likes given to
View All
Quick links