topic Re: ERROR in extracting date columns with certain format from a library and display only year. in SAS Programming

ERROR in extracting date columns with certain format from a library and display only year.

RTelang — Wed, 21 Dec 2016 07:33:24 GMT

hello my below code is extracting date columns having certain format and am displaying only year from the whole date but am facing an error that --->
'ERROR: Argument 1 to function YEAR referenced by the %SYSFUNC or %QSYSFUNC macro function is not
a number.
ERROR: Invalid arguments detected in %SYSCALL, %SYSFUNC, or %QSYSFUNC argument list. Execution
of %SYSCALL statement or %SYSFUNC or %QSYSFUNC function reference is terminated.
'

%macro dteyear(lib=,lib=,outdsn=);

proc sql noprint;
select distinct catx(".",libname,memname), name
into :dsns separated by " ", :varname separated by " "
from dictionary.columns
where libname = upcase("&lib") and format=('YYMMDD10.')
order by 1;
quit;
%put &dsns;
%put &varname;
%local olddsn curdsn curvbl i;
data &outdsn.;
set
%let olddsn=;
%do i=1 %to &sqlobs;
%let curdsn=%scan(&dsns,&i,%str( ));
%let curvbl=%scan(&varname,&i,%str( ));
%if &curdsn NE &olddsn
%then %do;
%if &olddsn NE
%then %do;
)
%end;
%let olddsn=&curdsn.;
&curdsn (keep=&curvbl
%end;
%else %do;
&curvbl
%end;
%end;
);
%do i=1 %to &sqlobs;
%scan(&varname,&i,%str( ))=%sysfunc(year("&varname"));
%end;
run;
proc print data=&outdsn;run;

%MEND prjdteyear;
%dteyear(lib=dte,outdsn=dteyr);

the input data is as follows

1975-12-04
1977-11-03
1989-09-15
1998-06-17
1999-05-31
2000-08-14
2001-03-11
2007-03-11
2007-12-28
2008-10-07
2009-12-03

desired output is-->

1975
1977
1989
1998
1999
2000
2001
2007
2007
2008
2009

any help please? this is urgent matter.

Re: ERROR in extracting date columns with certain format from a library and display only year.

Shmuel — Wed, 21 Dec 2016 07:40:51 GMT

It isn't clear what you have and why you use a macro .

What's wrong with the simple code:

data want;

infile datalines;

input date yymmdd10.

year = year(date);

keep year;

datalines;

1975-12-04
1977-11-03
1989-09-15
1998-06-17
1999-05-31
2000-08-14
2001-03-11
2007-03-11
2007-12-28
2008-10-07
2009-12-03

; run;

Re: ERROR in extracting date columns with certain format from a library and display only year.

chrej5am — Wed, 21 Dec 2016 07:50:29 GMT

I have similar questions as posted above, but from technical point of view you need to get rid of %sysfunc around the year.

You need to also change the format of the column to show correct results. For example the year 1975 will have your YYMMDD10. format applied and show as 1965-05-29 in proc print...

%macro dteyear(lib=,outdsn=);
proc sql noprint;
select distinct catx(".",libname,memname), name 
into :dsns separated by " ", :varname separated by " "
from dictionary.columns
where libname = upcase("&lib") and format=('YYMMDD10.')
order by 1;
quit;
%put &dsns;
%put &varname;
%local olddsn curdsn curvbl i;
data &outdsn.;
set 
%let olddsn=;
%do i=1 %to &sqlobs;
%let curdsn=%scan(&dsns,&i,%str( ));
%let curvbl=%scan(&varname,&i,%str( ));
%if &curdsn NE &olddsn
%then %do;
%if &olddsn NE
%then %do;
)
%end;
%let olddsn=&curdsn.;
&curdsn (keep=&curvbl
%end;
%else %do;
&curvbl
%end;
%end;
);
%do i=1 %to &sqlobs;
%scan(&varname,&i,%str( ))=year(&varname.);
%end;
run;
proc print data=&outdsn;run;
%MEND;

Re: ERROR in extracting date columns with certain format from a library and display only year.

Kurt_Bremser — Wed, 21 Dec 2016 07:52:54 GMT

%sysfunc(year("&varname"))

can never work. Macro knows only text, so it needs no quotes, therefore the quotes end up in the argument of the year function.

Even if you omit the quotes, it won't work, as %sysfunc(year()) is evaluated before the data step runs and the variable named by varname exists.

Since you are creating datastep code at this place in your macro, just write

%scan(&varname,&i,%str( ))=year(%scan(&varname,&i,%str( )));

to create the assignment statement.

Be aware that your final dataset will contain lots of missing values if your date variables have different names in the input datasets.

Re: ERROR in extracting date columns with certain format from a library and display only year.

Kurt_Bremser — Wed, 21 Dec 2016 07:56:52 GMT

And PLEASE!

stop writing such awful spaghetti code. Proper formatting with indentations makes the code much more readable.

Use the proper icons ({i} and "little running man") to preserve code formatting.

Re: ERROR in extracting date columns with certain format from a library and display only year.

RTelang — Wed, 21 Dec 2016 09:13:08 GMT

@Kurt_Bremser am getting the following output am not getting the specific year values.
Obs RFDTC
1 1965-05-19
2 1965-05-19
3 1965-05-19

Re: ERROR in extracting date columns with certain format from a library and display only year.

RTelang — Wed, 21 Dec 2016 09:19:59 GMT

@chrej5am yes i get 1965-05-29 in my proc print how do i get the original year values?

Re: ERROR in extracting date columns with certain format from a library and display only year.

Reeza — Wed, 21 Dec 2016 09:28:17 GMT

When you apply the YEAR function it returns a NUMBER. You've formatted your years as dates, not numbers.

Format year_var 8.;

Re: ERROR in extracting date columns with certain format from a library and display only year.

RW9 — Wed, 21 Dec 2016 09:35:41 GMT

I agree. I have mentioned this many times, however the user appears to be re-writing the enitre Base SAS library in macro only, and is unwilling to post test data or format code.

Re: ERROR in extracting date columns with certain format from a library and display only year.

RTelang — Wed, 21 Dec 2016 09:35:42 GMT

@Reeza it displays 1965 y it doesn't display the specified year.--->
%scan(&varname,&i,%str( ))=year(%scan(&varname,&i,%str( )));
format &varname 8.;