BookmarkSubscribeRSS Feed
RTelang
Fluorite | Level 6

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.

9 REPLIES 9
Shmuel
Garnet | Level 18

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;

chrej5am
Quartz | Level 8

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;

 

 

RTelang
Fluorite | Level 6
@chrej5am yes i get 1965-05-29 in my proc print how do i get the original year values?
Reeza
Super User

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

 

Format year_var 8.;

RTelang
Fluorite | Level 6
@Reeza it displays 1965 y it doesn't display the specified year.--->
%scan(&varname,&i,%str( ))=year(%scan(&varname,&i,%str( )));
format &varname 8.;
Kurt_Bremser
Super User
%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.

RTelang
Fluorite | Level 6
@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
Kurt_Bremser
Super User

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.

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 9 replies
  • 1233 views
  • 8 likes
  • 6 in conversation