DATA Step, Macro, Functions and more

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

Reply
Regular Contributor
Posts: 190

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

[ Edited ]

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.

Trusted Advisor
Posts: 1,566

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

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;

Contributor
Posts: 42

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

[ Edited ]

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;

 

 

Regular Contributor
Posts: 190

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

@chrej5am yes i get 1965-05-29 in my proc print how do i get the original year values?
Super User
Posts: 19,815

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

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

 

Format year_var 8.;

Regular Contributor
Posts: 190

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

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

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

%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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Regular Contributor
Posts: 190

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

Posted in reply to KurtBremser
@KurtBremser 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
Super User
Posts: 7,783

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

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super User
Super User
Posts: 7,955

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

Posted in reply to KurtBremser

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.

Ask a Question
Discussion stats
  • 9 replies
  • 304 views
  • 8 likes
  • 6 in conversation