topic Re: Loop through one table, use data to manipulate another table in SAS Procedures

Loop through one table, use data to manipulate another table

Wrathofmath — Fri, 20 Apr 2018 14:33:06 GMT

I have Table A which consists of: Name, Begin-Date, End-Date and I have table B which consists of: Name, Date.

I want to loop through Table B one row at a time, saving the name (VarDate) and date (VarDate) as variables. Using those saved variables, I want to create 3 tables. Table 1 is all records from Table A that are not VarName and the VarDAte is not between the Begin-Date and End-Date. The other 2 tables are identical: the record from Table A that is VarName with the Date between Begin-Date and End-Date. I will then Change the End-Date in table 2 to the VarDAte and the Begin-Date in Table 3 to VarDAte and union all 3 tables. Then go to the next row in Table B and do the same thing all over again.

I find I need to do it this way because Table B may list the same Name with multiple dates and I need the end file to show:

Name Begin-Date Date1

Name Date1 Date2

Name Date2 End-Date

as an example.

Any help with the coding or other suggestions is appreciated.

Re: Loop through one table, use data to manipulate another table

RW9 — Fri, 20 Apr 2018 15:11:49 GMT

Review the guidance on how to post a new question. Provide test data for each dataset in the form of a datastep, you can use this post to help:

https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-data-AKA-generate/ta-p/258712

We cannot see your data, nor guess what its like.

Next, post example using the test data, of what you want to see out at the end, again, we cannot guess this.

In general, a datastep or procedure is already a loop, so you should not really need to program a step by step, just logic to apply to all steps. Also, its generally not a good idea to split data up, then merge it back together again.

For instance, all data within a date range:

proc sql;
  create table want as 
  select b.*
  from   b
  right join a
  on       b.varname=a.name
  and     b.date between a.begin_date and a.end_date;
quit;

Re: Loop through one table, use data to manipulate another table

Wrathofmath — Fri, 20 Apr 2018 19:09:57 GMT

data sashelp.file1;
infile datalines dsd truncover;
input Name:$13. BeginDate:mmddyy10. EndDate:mmddyy10.;
datalines4;
Bob,05/03/2007,07/18/2016
Carol,06/07/2008,08/09/2010
Evan, 03/02/2004,12/24/2016
Trevor, 06/26/2009,06/26/2010
Tyler, 07/17/2007, 09/28/2011
;;;;

data sashelp.file2;
infile datalines dsd truncover;
input Name:$13. Date:mmddyy10. ;
datalines4;
Evan, 07/08/2007
Evan, 02/02/2010
Tyler, 05/11/2009
;;;;

data sashelp.file3;
infile datalines dsd truncover;
input Name:$13. BeginDate:mmddyy10. EndDate:mmddyy10.;
datalines4;
Bob,05/03/2007,07/18/2016
Carol,06/07/2008,08/09/2010
Evan, 03/02/2004,7/8/2007
Evan, 7/8/2007,2/2/2010
Evan, 2/2/2010,12/24/2016
Trevor, 06/26/2009,06/26/2010
Tyler, 07/17/2007, 5/11/2009
Tyler, 5/11/2009, 09/28/2011
;;;;

I couldn't really get the program to work but I think this should at least get you where you need to be.

Essentially, I am trying to break up file 1 with information from file 2. For every name and date in File 2, I need to locate the row in File 1, break it up into 2 data rows, and replace the BeginDate in one row with the new date and EndDate in the other row with the new Date.

I have tried to use data steps but I cannot see how it will work in this instance because, for example Evan, needs to have the split done for the first date in File 2 before going to the second split. But maybe there is an advanced data set expression that can handle what I need it to do. Thanks for you help.

Re: Loop through one table, use data to manipulate another table

RW9 — Mon, 23 Apr 2018 08:15:13 GMT

I don't have time to write the whole process, but to get your started:

data _null_;
  set file2;
  call execute('data want'||strip(put(_n_,best.))||'; 
                  set file1;
                  if name="'||strip(name)||'" and... then do;
                    ...;
                  end;
                run;');
run;

This will generate a datastep for each row in file2, which will then get executed after the data _null_ is finished. So your effectively using file2 to generate all the code needed. This is an advanced topic however.