topic Re: Conditionally fill column cells using values in another column for each customer in SAS Programming

Conditionally fill column cells using values in another column for each customer

ridi — Thu, 12 Dec 2024 17:37:49 GMT

Hello, I have a dataset where I am trying to fill in the date value using values in another column . The condition I am trying to apply is for each ID, if any one row is blank then leave blank. Fill in date value if all rows has date. Can you please help with the sas code.

Here is the variable attribute and table I have and want. There are other variables as well in the actual dataset.

Variable	Type	Len	Format	Informat
ID	Num	8	BEST.
End Date	Num	8	MMDDYY10.	MMDDYY10.

Problem 3
Data: Have3		Data: Want3
ID	End Date		ID	End Date	End Date1
1			1
1	12/24/2023		1	12/24/2023
1
1	12/24/2023		1	12/24/2023
2	1/13/2005		2	1/13/2005
2	1/13/2005		2	1/13/2005
2			2
3			3
3	3/5/2017		3	3/5/2017
3			3
3			3
3			3
4			4
4			4
4			4
5	5/5/2020		5	5/5/2020	5/5/2020
5	5/5/2020		5	5/5/2020	5/5/2020
6	11/1/2024		6	11/1/2024	11/1/2024

Thank you.

Re: Conditionally fill column cells using values in another column for each customer

PaigeMiller — Thu, 12 Dec 2024 18:07:43 GMT

/* UNTESTED CODE */
proc summary data=have3 nway;
    class id;
    var enddate;
    output out=counts nmiss=nmiss;
run;

data want3;
    merge have3 counts(drop=_type_ _freq_);
    by id;
    if nmiss=0 then enddate1=enddate;
run;

The code above is UNTESTED since your data is not in a usable form. From now on, please provide data as text in working SAS data step code (examples and instructions). We cannot work with Excel files, screen captures, or copy/paste from Excel,

Re: Conditionally fill column cells using values in another column for each customer

Patrick — Fri, 13 Dec 2024 00:16:06 GMT

Below should work. And yes, going forward please try and provide sample data in directly usable form similar to below data step Have. This helps us help you.

data have;
	infile datalines truncover dsd dlm=' ';
	input ID : 8. End_Date_have : mmddyy10. End_Date_want : mmddyy10.;
	format End_Date_have End_Date_want date9.;
	datalines;
1
1 12/24/2023
1
1 12/24/2023
2 1/13/2005 
2 1/13/2005
2
3
3 3/5/2017 
3
3
3
4
4
4 
5 5/5/2020 5/5/2020
5 5/5/2020 5/5/2020
6 11/1/2024 11/1/2024
;
run;

data want;
	if _n_=1 then
		do;
			dcl hash h1(dataset:'have(where=(missing(end_date_have)))');
			h1.defineKey('id');
			h1.defineDone();
		end;
	set have;
	if h1.check() ne 0 then end_date_derived=end_date_have;
	format end_date_derived date9.;
run;

proc print data=want;
run;

Re: Conditionally fill column cells using values in another column for each customer

Patrick — Fri, 13 Dec 2024 00:21:38 GMT

@PaigeMiller I started to copy/paste data provided as tables into Copilot asking for a SAS data step. This often works quite well.

Re: Conditionally fill column cells using values in another column for each customer

Ksharp — Fri, 13 Dec 2024 02:19:02 GMT

Using Patrick's data step to get table, and WANT variable is what you are looking for .

data have;
	infile datalines truncover dsd dlm=' ';
	input ID : 8. End_Date_have : mmddyy10. End_Date_want : mmddyy10.;
	format End_Date_have End_Date_want date9.;
	datalines;
1
1 12/24/2023
1
1 12/24/2023
2 1/13/2005 
2 1/13/2005
2
3
3 3/5/2017 
3
3
3
4
4
4 
5 5/5/2020 5/5/2020
5 5/5/2020 5/5/2020
6 11/1/2024 11/1/2024
;
run;

proc sql;
create table want as
select *,case when count(*)=n(End_Date_have) then End_Date_have else . end as want format=date9.
 from have 
  group by id;
quit;

Re: Conditionally fill column cells using values in another column for each customer

PaigeMiller — Fri, 13 Dec 2024 10:20:09 GMT

@Patrick wrote:

@PaigeMiller I started to copy/paste data provided as tables into Copilot asking for a SAS data step. This often works quite well.

In my opinion, it is the responsibility of the original poster to provide data in usable form if they want code that works; it is not my responsibility to convert it.

Re: Conditionally fill column cells using values in another column for each customer

mkeintz — Sun, 15 Dec 2024 01:32:22 GMT

Your data are sorted by ID. You want new variable END_DATE1 to take the value of END_DATE only for ID's than have no instance of missing END_DATE, correct?

If so, then

data want;
  merge have (where=(end_date=.)  in=inblank)
        have;
  by id;
  format end_date1 date9.  ;
  if inblank=0 then end_date1=end_date;
run;

The temporary dummy variable INBLANK will be zero only for those ID's with no rows with missing END_DATE.