topic Re: how to keep the value of a certain date when appending large volume of data in SAS Programming

how to keep the value of a certain date when appending large volume of data

kevsma — Thu, 02 Mar 2023 17:31:04 GMT

Hello,

I want to append a long list of data files and then to keep only the values from certain files as the final dataset I am using. Below is my code:

%macro loop(out=total,from=,to=);
%local fromdate todate i month ;
%let fromdate = %sysfunc(inputn(&from.01,yymmdd6));
%let todate = %sysfunc(inputn(&to.01,yymmdd6));
%put &=fromdate %sysfunc(putn(&fromdate,date9));
%put &=todate %sysfunc(putn(&todate,date9));
%do i= 0 %to %sysfunc(intck(month,&fromdate,&todate));
  %let month=%sysfunc(intnx(month,&fromdate,&i),YYMMN4);
  data current; set LCMF.cm20&month (keep=UCI Status RCAbrv birthday); month=&month.; uci_0=input(uci,12.); run;
  proc append data=current base=&out force; run; 
%end;
%mend;

proc delete data=total ; run;
%loop(out=total,from=1708,to=2207);

When appending those files, I want to only keep the value of column "birthday" from the files 1807, 1907, 2007, 2107, 2207. Is there a way to build that in the above macros?

Re: how to keep the value of a certain date when appending large volume of data

Tom — Thu, 02 Mar 2023 18:14:28 GMT

So what CODE do you want the macro to generate?

It sounds like instead of generating this:

data current; set LCMF.cm20&month (keep=UCI Status RCAbrv birthday);

You want to generate this for some selected values of &MONTH.

data current; set LCMF.cm20&month (keep=UCI Status RCAbrv );

So perhaps you want:

data current;
 set LCMF.cm20&month (keep=UCI Status RCAbrv
%if %substr(&month,3,2) ne 07 %then birthday ;
);

Re: how to keep the value of a certain date when appending large volume of data

kevsma — Thu, 02 Mar 2023 19:21:30 GMT

Thanks @Tom I tried your code, but it only grabs birthday when month doesn't end with 07, which is the opposite of what I want. I want to only keep birthday when month ends with 07, that means birthday will be missing (for exmaple) when month does not end with 07. Hope that makes sense.

Re: how to keep the value of a certain date when appending large volume of data

Reeza — Thu, 02 Mar 2023 19:37:26 GMT

Change the NE (not equals) to EQ (equals) from Tom's code?

Re: how to keep the value of a certain date when appending large volume of data

kevsma — Thu, 02 Mar 2023 19:38:34 GMT

I did, but it's not working anymore. the "birthday" column was dropped all together.

Re: how to keep the value of a certain date when appending large volume of data

Tom — Thu, 02 Mar 2023 19:40:48 GMT

@kevsma wrote:

Thanks @Tom I tried your code, but it only grabs birthday when month doesn't end with 07, which is the opposite of what I want. I want to only keep birthday when month ends with 07, that means birthday will be missing (for exmaple) when month does not end with 07. Hope that makes sense.

You lost me. Explicit examples would help.

Is the issue that the variable is NOT there sometimes? When?

Is the issue that the variable is always there , but sometimes you want to ignore it, or replace its value with missing values? When?

Since you are running a data step it might just be easier to conditionally generate a data step statement that sets the value to missing.

data ....
%if %substr(&month,3,2)=07 then %do;
  birthday=.;
%end;
...

This has the added advantage of creating a dataset with the same set of variables in both cases so that PROC APPEND does not complain or do the wrong thing.

Re: how to keep the value of a certain date when appending large volume of data

kevsma — Thu, 02 Mar 2023 19:42:32 GMT

Sorry @Tom for confusing you. "Birthday" column is included in all data files I am appending, but I only want to use the value of birthday when the month ends with 07.

Re: how to keep the value of a certain date when appending large volume of data

Tom — Thu, 02 Mar 2023 19:46:26 GMT

@kevsma wrote:

Sorry @Tom for confusing you. "Birthday" column is included in all data files I am appending, but I only want to use the value of birthday when the month ends with 07.

You should be able to figure it out then. Sounds like either

keep= ...
%if %substr(&month,3,2)=07 %then birthday;
...

%if %substr(&month,3,2) ne 07 then %do;
  call missing(birthday);
%end;

Re: how to keep the value of a certain date when appending large volume of data

kevsma — Thu, 02 Mar 2023 19:55:37 GMT

thanks @Tom the first method doesn't work as birthday was not picked at all.

for your second suggestion, could you please indicate where to insert that %do step? i am having a hard time inserting that snippet into the proper location, thanks again...

Re: how to keep the value of a certain date when appending large volume of data

Reeza — Thu, 02 Mar 2023 19:59:16 GMT

options mprint;

%macro loop(out=total, from=, to=);
	%local fromdate todate i month;
	%let fromdate = %sysfunc(inputn(&from.01, yymmdd6));
	%let todate = %sysfunc(inputn(&to.01, yymmdd6));
	%put &=fromdate %sysfunc(putn(&fromdate, date9));
	%put &=todate %sysfunc(putn(&todate, date9));

	%do i=0 %to %sysfunc(intck(month, &fromdate, &todate));
		%let month=%sysfunc(intnx(month, &fromdate, &i), YYMMN4);

		data current;
			set LCMF.cm20&month (keep=UCI Status RCAbrv birthday);
			month=&month.;
			uci_0=input(uci, 12.);
			%if %substr(&month, 3, 2)=07 %then %do;
				drop birthday;
				*call missing(birthday);
			%end;
		run;

		proc append data=current base=&out force;
		run;

	%end;
%mend;

proc delete data=total;
run;

%loop(out=total, from=1708, to=2207);

1. Format your code, makes it much easier to work with

2. If you drop the variable you will generate a warning on the PROC APPEND FORCE. I believe you can turn it off with an option if you want. Another alterntive, commented out is to set it to missing.

3. This was tested and works.

Re: how to keep the value of a certain date when appending large volume of data

Tom — Thu, 02 Mar 2023 20:14:07 GMT

Indentation does make the code much easier to read. But it helps to keep the indentations aligned.

Notice how the %IF is not intended at the same left as the %LET.

Keeping the indentation of the SAS code and the MACRO code independent will make it much easier to follow the logic of both.

%macro loop(out=total, from=, to=);
	%local fromdate todate i month;
	%let fromdate = %sysfunc(inputn(&from.01, yymmdd6));
	%let todate = %sysfunc(inputn(&to.01, yymmdd6));
	%put &=fromdate %sysfunc(putn(&fromdate, date9));
	%put &=todate %sysfunc(putn(&todate, date9));

	%do i=0 %to %sysfunc(intck(month, &fromdate, &todate));
		%let month=%sysfunc(intnx(month, &fromdate, &i), YYMMN4);

  data current;
	set LCMF.cm20&month (keep=UCI Status RCAbrv birthday);
	month=&month.;
	uci_0=input(uci, 12.);
		%if %substr(&month, 3, 2)=07 %then %do;
	drop birthday;
	*call missing(birthday);
		%end;
  run;

  proc append data=current base=&out force;
  run;

	%end;
%mend;

Now the %IF is aligned with the %LET and the DROP is aligned with the SET.

Personally I don't indent the body of a macro , but that is because I normally write autocall macros where the macro definition is its own file and it would a silly waste of line space to indent every line in a file.

Re: how to keep the value of a certain date when appending large volume of data

kevsma — Thu, 02 Mar 2023 21:45:10 GMT

Thanks @Tom @Reeza Below is the code that works. If i may ask a follow-up question, what if i want to fill in the missing birthdays with the values from files when month ends with 07? Currently using the following code, birthdays will be missing if month doesn't end with 07, what if i want to fill in those missing birthdays using values from 07 files by ID (uci)?

%macro loop(out=total,from=,to=);
%local fromdate todate i month ;
%let fromdate = %sysfunc(inputn(&from.01,yymmdd6));
%let todate = %sysfunc(inputn(&to.01,yymmdd6));
%put &=fromdate %sysfunc(putn(&fromdate,date9));
%put &=todate %sysfunc(putn(&todate,date9));

%do i= 0 %to %sysfunc(intck(month,&fromdate,&todate));
  %let month=%sysfunc(intnx(month,&fromdate,&i),YYMMN4);

  data current; 
	set LCMF.cm20&month (keep=UCI Status RCAbrv birthday); 
		month=&month.; 
		uci_0=input(uci,12.); 
		%if %substr(&month, 3, 2) ne 07 %then %do;
			call missing (birthday);
		%end;
	run;

  	proc append data=current base=&out force; 
  	run;

  %end;
%mend;

proc delete data=total ; run;
%loop(out=total,from=1708,to=1807);

Re: how to keep the value of a certain date when appending large volume of data

Reeza — Thu, 02 Mar 2023 22:28:22 GMT

I'd probably do that outside of this step. Are you assuming each years values would be filled in? You can create a subset and then merge the data back in is probably the easiest method.

Re: how to keep the value of a certain date when appending large volume of data

s_lassen — Fri, 03 Mar 2023 08:49:37 GMT

Maybe it is easier to just set all the datasets in a single step:

%macro indata(from=,to=);
  %do from=&from %to &to;
    %if %substr(&from,3)=13 %then
       %let from=%eval(&from+88);
    %do; LCMF.cm20&from(keep=UCI Status RCAbrv %if %substr(&from,3)=7 %then birthday;)%end; /* %do..%end; to avoid spurious blanks */
    %end;
%mend;

data total;
  set %indata(from=1708,to=2207) indsname=_name;
  month=input(substr(_name,length(_name)-4),4.0);
  uci_0=input(uci,12.);
run;

That way, you don't have to worry about BIRTHDAY not being in the first dataset you read, it will get there anyway.