figuring out a way to force macro to return a numeric value as output

[m_storer_720]

Good afternoon,

I know there is some way to do this via %SYSFUNC (or so I have heard), but I am having a very difficult time getting much to work and was wondering if anyone else here has ever encountered this same problem.

Basically, all I need to do is have this macro return a numeric result, so that I can utilize it something like:

data s1 ;
date_x = 22;
date_y = 33;
date_z = %getepoch(path) ;
run;quit;

The point of the macro itself is simply to derive the Unix epoch time for a supplied (non-epoch'ed) date.

I have most of what I need already working, but I still don't think the macro is returning an assignable value.

An additional problem or question might be:  is there any way to replicate the Powershell execution piece of functionality without invoking 'filename process pipe' ?

I think that section in particular is breaking something in the SAS interpreter. I have pasted a screenshot of that at the following URL:  output-log-d — ImgBB (ibb.co)

[actual URL plaintext =  https://ibb.co/K9hssG3]

Code snippet is provided below. Side note: I'm pretty sure my numeric format or bestd20 is wrong somehow (still working on that). Thanks in advance if anyone has some ideas on how to fix this.

/*       code       */ 

%macro getepoch(twopath);
%let twopath = %sysfunc(quote(%qsysfunc(dequote(&path))));
filename process pipe
"powershell -File C:\Temp\gepoch.ps1 "&twopath."";

data processes;
infile process ;
length
time 8;
format time bestd20. ;
input time;
run ; quit;
proc sql;
select time into :t2 from processes ;
quit;
%put &t2. ;
%mend getepoch;

%let path="C:\Temp\output9.txt" ;
%getepoch(path) ;

data s1 ;
x = 22;
y = 33;
z = %getepoch(path) ;
run;quit;

[PaigeMiller]

Is macro variable &t2 inside macro %getepoch a number? Seems like an important detail which you haven't mentioned.

If it is a number, you can add (at the top of your code)

%local t2;

then run the macro and next in your data step 

z=&t2;

If it is not a number, then we can't help you.

[m_storer_720]

Hi Paige,

Thank you for the reply. Yes I can confirm that, in this case, &t2 would be a Numeric / BESTD20.

I have used the %local variables before, but I was trying to do this in such a way that I can just dynamically call the macro anywhere in open code and be guaranteed a result.

I think your idea *would* work - I would probably just have to declare several %local variables up front (for as many rows as I need to generate later), and then do my top 20 or top 30 data sets with included %gepoch'ed return values manually specified within the individual data steps.

To be fair, the entire solution as I have devised it may be a bit overly complex. Forgive me if I am generating more wood chips than wood. I am still relatively new to SAS.

Thanks again

[PaigeMiller]

TYPO 

I should have used %GLOBAL and not %LOCAL

I have used the %local variables before, but I was trying to do this in such a way that I can just dynamically call the macro anywhere in open code and be guaranteed a result.

Again, I should have said %GLOBAL, and then you can do exactly what you just said, your value of &t2 can be used anywhere.

[ballardw]

It might help to describe what the purpose is.

Pretty much can guarantee and macro that calls a data step or other procedure will not do what you want with an assignment inside a data step. As soon as the macro generated data step or proc boundary is found then the previous data step calling the macro will 1) terminate 2) likely with a syntax error because what followed the Z= was not a valid assignment: 

Your example code would include the "statement"

Z = filename process pipe
"powershell -File C:\Temp\gepoch.ps1 "&twopath."";

which is not a valid call to the FILENAME function in a data step.

When I see statements like " (for as many rows as I need to generate later), and then do my top 20 or top 30 data sets with included " one starts suspecting the problem definition needs to be revised as "generating rows" is maybe better accomplished outside of macro coding.

[yabwon · Accepted solution]

How about wrapping 4GL in a %sysfunc(DoSubL(...)) sandwich?

%macro getepoch(path);
%local t2;
%LET RC = %SYSFUNC(DOSUBL(%STR( /* <- sandwich start */
filename process pipe
"powershell -File C:\Temp\gepoch.ps1 ""&path.""";
data processes;
infile process ;
length
time 8;
format time bestd20. ;
input time;
run ; quit;
proc sql;
select time into :t2 from processes ;
quit;
))); /* <- sandwich end */
&t2.
%mend getepoch;

%let path=C:\Temp\output9.txt ;
%getepoch(&path.) ;

data s1 ;
x = 22;
y = 33;
z = %getepoch(&path.) ;
run;quit;

Bart

[PaigeMiller]

I did not realize that DOSUBL would work like this inside of a macro. So now I have caught up to @Kurt_Bremser , I have learned a new thing today: a new use case for DOSUBL.

This is still quite a lot of arcane programming just to make &t2 available outside of the the macro, which is "less arcane" programming. But the idea of calling the macro wherever the user wants to call it, such as within a data step, still bothers me when you could just use &t2 in that data step. However, if that's what the OP wants...

[m_storer_720]

Yabwon,

Thank you for the help. This is a really clever solution, and it seems to test just fine on my system. 

[Reeza]

Can we assume that you've already confirmed there's no way to accomplish this within SAS already?

>The point of the macro itself is simply to derive the Unix epoch time for a supplied (non-epoch'ed) date.

[Reeza]

https://blogs.sas.com/content/sasdummy/2015/04/16/how-to-convert-a-unix-datetime-to-a-sas-datetime/

https://communities.sas.com/t5/SAS-Enterprise-Guide/EPOCH-Time-conversion/td-p/104154

I would guess that if you can go in one direction you can go in the other.

[m_storer_720]

Hello Reeza,

good question with respect to eliminating the possibility of not being able to just do this in straight SAS.

The initial need for me to write the Powershell wrapper for epoch'ed times was to devise some kind of a workaround to the classic Windows DST time and datestamps on files issue.

So I guess the genesis of this whole thing comes from having to bootstrap some way to interrogate a given Windows filesystem as to exactly what the time was on a file / was it DST'ed or No / and then proceed onward to the next function of a larger, proper SAS DI job.

Of course, we have a couple different SAS jobs that pull file metadata in from Windows but the root problem was that Windows, as an OS does not always accurately self report.