BookmarkSubscribeRSS Feed
Lucas
Calcite | Level 5
Hi,
I have a DataSet with column col_EXP, which contains date expression, e.g:

DATA test;
col_EXP="intnx('day',today(),2)";output;
col_EXP="intnx('day',today(),10)";output;
RUN;

Now, I would like to create a new column col_DT with computed date from expression in column col_EXP.

data test2;
set test;
col_DT = ????;
run;

I have tried with call symput, symget and resolve funcions but without any success:(
Can anyone help me?
22 REPLIES 22
art297
Opal | Level 21
Lucas,

There has GOT to be a less convoluted way than the one I'll propose but, if this is something that you HAVE to get done, the following might provide what you need:


DATA test;
length col_EXP $50;
col_EXP="intnx('day',today(),2)";output;
col_EXP="intnx('day',today(),10)";output;
RUN;

filename ft17f001 temp;
data _null_;
file ft17f001;
set test end=lastone;
if _n_ eq 1 then do;
put 'data test2;';
put +3 'format col_DT date9.;';
end;
put +3 'col_DT=' col_EXP ';';
put +3 'output;';
if lastone then put 'Run;';
run;
%inc ft17f001 / source2;

data test2;
set test;
set test2;
run;

HTH,
Art
SPR
Quartz | Level 8 SPR
Quartz | Level 8
Hello Lucas,

I am not quite sure why you use this approach. Could you explain your goal in more detail? Anyway, this can be achieved with macro variables like this:
[pre]
DATA _null_t;
%let col_EXP1=intnx('day',today(),2);
%let col_EXP2=intnx('day',today(),10);
RUN;
data Result;
col_DT = &col_EXP1; ouput;
col_DT = &col_EXP2; ouput;
format col_DT date7.;
run;
[/pre]
I do not think it is what you need. This is why I' like to know more details.
Sincerely,
SPR
polingjw
Quartz | Level 8
Here is an alternative macro solution, although no less convoluted.

[pre]
DATA test;
length col_EXP $50;
col_EXP="intnx('day',today(),2)";output;
col_EXP="intnx('day',today(),10)";output;
RUN;

proc sql noprint;
select count(*) into: num_expressions
from test;

%let num_expressions = &num_expressions;

select col_EXP into :col_EXP1-:col_EXP&num_expressions
from test;
quit;

%macro test;
%do i = 1 %to &num_expressions;
col_EXP ="&&&col_exp&i";
col_DT = &&&col_exp&i;
output;
%end;
%mend;

data test1;
%test
run;
[/pre]
Lucas
Calcite | Level 5
Hi,
thank you all for a quick response! Sorry for my english 😉


@art2897
Thanks! I wrote something similar to your program. Tommorow I will try to paste my code. It works fine but as you said it is quite complex. How works 'put +3' statement in your code? 🙂

@SPR
The expression must be define in a permanent DataSet. Based on this expression a newcolumn col_DT is computed. I hope I explained problem more precisely.

@polingjw
yeah:) your macro is convoluted. Great exercise to practise macro variables 🙂

================================
Let's try to find a solution in one DataStep! 🙂
I have tried something like this (in many many combinations :/):

DATA test;
col_EXP="intnx('day',today(),2)";output;
col_EXP="intnx('day',today(),10)";output;
RUN;

DATA test2;
set test;
call symput ('col_EXP_DT', col_EXP);
col_DT = symget('col_EXP_DT');
RUN;

But unfortunately symget does not resolved the expression 😞
Cynthia_sas
SAS Super FREQ
Hi:
About this code:
[pre]
DATA test2;
set test;
call symput ('col_EXP_DT', col_EXP);
col_DT = symget('col_EXP_DT');
RUN;
[/pre]

You cannot both create and use a macro variable within 1 DATA step program. That means if you create &col_EXP_DT in a DATA step program, that a step boundary must be encountered before you USE &col_EXT_DT in a program.

See this Tech Support note (and the Macro Facility documentation) for more details:
http://support.sas.com/kb/23/182.html

cynthia
chang_y_chung_hotmail_com
Obsidian | Level 7
...
> You cannot both create and use a macro variable within
> 1 DATA step program. That means if you create
> &col_EXP_DT in a DATA step program, that a step
> boundary must be encountered before you USE
> &col_EXT_DT in a program.
>
> See this Tech Support note (and the Macro Facility
> documentation) for more details:
> http://support.sas.com/kb/23/182.html
...
I guess the note is not 100% clear if even a long time user ends up drawing a wrong conclusion like that. You definitely *can* both create and use a macro variable within a data step.
[pre]
%symdel mvar;
data _null_;
call symput('mvar', 'what');
something = resolve('&mvar');
put something=;
run;
/* on log
something=what
*/
[/pre] Message was edited by: chang_y_chung@hotmail.com
Cynthia_sas
SAS Super FREQ
The posted TS Note says that:

You cannot use a macro variable reference to retrieve the value of a macro variable


What they mean is a "simple" &macvar form of reference. There are other techniques, as you have shown. But the most common issue is trying to use a simple &macvar in the same step where it is created.

cynthia
Ksharp
Super User
There is another problem.
You need assign long enough space for variable col_EXP.

[pre]
DATA test;
length col_EXP $ 100;
col_EXP="intnx('day',today(),2)";output;
col_EXP="intnx('day',today(),10)";output;
RUN;

DATA test2;
set test;
call symput (cats('col_EXP_DT',_n_), col_EXP);
col_DT = symget('col_EXP_DT');
RUN;
%put _user_;
[/pre]



Ksharp
Cynthia_sas
SAS Super FREQ
What about the invalid argument message with your code? cynthia
[pre]
492
493 DATA test2;
494 set test;
495 call symput (cats('col_EXP_DT',_n_), col_EXP);
496 col_DT = symget('col_EXP_DT');
497 RUN;

NOTE: Invalid argument to function SYMGET at line 496 column 10.
col_EXP=intnx('day',today(),2) col_DT= _ERROR_=1 _N_=1
NOTE: Invalid argument to function SYMGET at line 496 column 10.
col_EXP=intnx('day',today(),10) col_DT= _ERROR_=1 _N_=2
NOTE: There were 2 observations read from the data set
WORK.TEST.
NOTE: The data set WORK.TEST2 has 2 observations and 2
variables.
NOTE: DATA statement used (Total process time):
real time 0.14 seconds
cpu time 0.01 seconds
[/pre]
Ksharp
Super User
Cynthia.
I have not received these NOTEs.
I am using SAS 9.2 TS1M0 for Windows.
The following is my LOG.

[pre]
132
133 DATA test2;
134 set test;
135 call symput (cats('col_EXP_DT',_n_), col_EXP);
136 col_DT = symget('col_EXP_DT');
137 RUN;

NOTE: There were 2 observations read from the data set WORK.TEST.
NOTE: The data set WORK.TEST2 has 2 observations and 2 variables.
NOTE: DATA statement used (Total process time):
real time 0.27 seconds
cpu time 0.01 seconds


138 %put _user_;
GLOBAL COL_EXP_DT intnx('day',today(),2)
GLOBAL COL_EXP_DT1 intnx('day',today(),2)
GLOBAL COL_EXP_DT2 intnx('day',today(),10)
[/pre]




Ksharp
Cynthia_sas
SAS Super FREQ
How did you get &COL_EXP_DT (without a number) in your global symbol table?? You appended _N_ to the macro variable name in the CALL SYMPUT statement in your program. That should have created ONLY &COL_EXP_DT1 and &COL_EXP_DT2.

If &COL_EXP_DT (without a number) does not exist, then you will get the invalid argument message. I suspect the reason you're not seeing the message is that in some previous test run you actually created &COL_EXP_DT.

But really, it's a moot point about the message, because I believe the original poster did NOT just want to see the text string with the INTNX function, I think he actually wanted to have the INTNX function execute. And, I agree with ArtC and other responders who have indicated that what is being shown 1) doesn't make sense and 2) probably doesn't require a macro variable.

I just don't think a lot of time needs to be spent coming up with more variations to do something that is not well-explained on data that is not fully understandable. If we understood a bit more about the real data and the real scenario and the actual task to be accomplished, someone might have a useful suggestion.

cynthia
Ksharp
Super User
Cynthia.
&COL_EXP_DT (without a number) is actually from OP's code and I revise it,then got this curious result.
And I receive these notes as yours.
When I close SAS session and re-run the code,the following is LOG.

[pre]

6
7 DATA test2;
8 set test;
9 call symput (cats('col_EXP_DT',_n_), col_EXP);
10 col_DT = symget('col_EXP_DT');
11 RUN;

NOTE: Invalid argument to function SYMGET at line 10 column 10.
col_EXP=intnx('day',today(),2) col_DT= _ERROR_=1 _N_=1
NOTE: Invalid argument to function SYMGET at line 10 column 10.
col_EXP=intnx('day',today(),10) col_DT= _ERROR_=1 _N_=2
NOTE: There were 2 observations read from the data set WORK.TEST.
NOTE: The data set WORK.TEST2 has 2 observations and 2 variables.
NOTE: DATA statement used (Total process time):
real time 0.22 seconds
cpu time 0.05 seconds


12 %put _user_;
GLOBAL COL_EXP_DT1 intnx('day',today(),2)
GLOBAL COL_EXP_DT2 intnx('day',today(),10)

[/pre]

and maybe change it a little more.

[pre]
DATA test2;
set test;
call symput (cats('col_EXP_DT',_n_), col_EXP);
col_DT = symget(cats('col_EXP_DT',_n_));
RUN;
%put _user_;
[/pre]
then These notes will not appear again.

[pre]
18
19 DATA test2;
20 set test;
21 call symput (cats('col_EXP_DT',_n_), col_EXP);
22 col_DT = symget(cats('col_EXP_DT',_n_));
23 RUN;

NOTE: There were 2 observations read from the data set WORK.TEST.
NOTE: The data set WORK.TEST2 has 2 observations and 2 variables.
NOTE: DATA statement used (Total process time):
real time 0.05 seconds
cpu time 0.02 seconds


24 %put _user_;
GLOBAL COL_EXP_DT1 intnx('day',today(),2)
GLOBAL COL_EXP_DT2 intnx('day',today(),10)
[/pre]



Ksharp
ArtC
Rhodochrosite | Level 12
Aside that this does not work the way you want, again we ask what are you trying to do?

If you want to create a new variable then no macro variables are needed.
[pre]
DATA test;
col_EXP="intnx('day',today(),2)";output;
col_EXP="intnx('day',today(),10)";output;
RUN;

DATA test2;
set test;
col_DT = col_exp;
RUN;
[/pre]
But even this does not make any sense.
Ksharp
Super User
Macro statement can solve your problem.
And I think 'call symput, symget, resolve' would not solve your problem,because you request to dynamic invoke Macro variables ,it is hard to achieve.

[pre]

DATA test;
length col_EXP $ 100;
col_EXP="intnx('day',today(),2)";output;
col_EXP="intnx('day',today(),10)";output;
RUN;

DATA _null_;
set test nobs=num_obs;
call symput (cats('col_EXP_DT',_n_), col_EXP);
call symput ('nobs',num_obs);
RUN;
%put _user_;

%macro data;
data test2;
set test;
if _n_ eq 1 then col_DT=&col_EXP_DT1;
%do i=2 %to &nobs;
else if _n_ eq &i then col_DT=&&col_EXP_DT&i ;
%end;
format col_DT yymmdd10.;
run;
%mend;

%data

proc print;run;
[/pre]



Ksharp Message was edited by: Ksharp

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
  • 22 replies
  • 3358 views
  • 0 likes
  • 8 in conversation