How to write a value in macro variable to CSV file

[learn_SAS_23]

 Hello Team , 

i would like to find answers for below questions 

1. store  three different values ,separated by semicolon  in to macro variable 

Output=catx(";","Text_available", compress(param1),compress(param2)));

while doing this am getting an error as below 

ERROR 180-322: Statement is not valid or it is used out of proper order.

this will suppress , if i use coma instead of semicolon .

But i need to separate three values by semicolon(;) and write them to a CSV file.

2. is it possible to write a value in macro variable to a CSV file ?

please post your valuable suggestions 

[PaigeMiller]

---

@learn_SAS_23 wrote:

 Hello Team , 

i would like to find answers for below questions 

1. store  three different values ,separated by semicolon  in to macro variable 

Output=catx(";","Text_available", compress(param1),compress(param2)));

while doing this am getting an error as below 

ERROR 180-322: Statement is not valid or it is used out of proper order.

---

Show us the full LOG for this DATA step. DO NOT show us errors in the LOG without showing us the entire log for the DATA step.

But i need to separate three values by semicolon(;) and write them to a CSV file.

2. is it possible to write a value in macro variable to a CSV file ?

This is really pointless to create a macro variable from a SAS data set, then try to write the macro variable to a CSV file. You can write the CSV file directly from the data set.

[Tom]

Please show the code you are using.

If you write variables to the CSV file then the delimiters specified in the FILE statement are properly inserted.  So split your macro into individual values and write them.

So if you have a value like:

%let mvar=%str(Text_available;Parm1 Value;Parm2 Value);

Here is a snippet you might include in the proper place in your dataset to write three values to the CSV file:

do i=1 to 3;
   value = scan("&mvar",i,';');
   put value @;
end;

[learn_SAS_23]

Hello All, Here is the log of the program

36 %STPBEGIN
37 %global Param1 Param2;
38 %global status result_1 ;
39
40 data UI_Result;
41 %let dt = &sysdate;
42 format status $5.;
43 format result_1 $200.;
44
45 format parametri1 $20.;
46 format parametri2 $20.;
47
The SAS System

48 parametri1="&Param1.";
49 parametri2="&Param2.";
50
51 if strip(parametri1) in ('A','B','C','D') and parametri2 in (0,1,2,3,4) then do;
52 status="OK";
53 viesti=cat(catx(";","Test_available", compress(parametri1),compress(parametri2)));
54 end;
55 else do;
56 status="Error";
57 viesti=cat("Invalid input parameters ", compress(parametri1), " and ", compress(parametri2), ".");
58
59 end;
60
61 call symput ('status', status);
62 call symput ('result_1', result_1 );
63
64
65 run;

NOTE: Character values have been converted to numeric values at the places given by: (Line):(Column).
51:51
NOTE: The data set WORK.UI_Result has 1 observations and 4 variables.
NOTE: DATA statement used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds

66
67 %put &status.;
OK
68 %put &viesti.;
Test_available
NOTE: Line generated by the macro variable "result_1".
68 Test_available;A;1
_
180
ERROR 180-322: Statement is not valid or it is used out of proper order.

The SAS System

NOTE: Line generated by the macro variable "result_1".
68 Test_available;A;1
_
180
ERROR 180-322: Statement is not valid or it is used out of proper order.

69
70 %STPEND
71
72 * Begin EG generated code (do not edit this line);
73 ;*';*";*/;quit;
74 %STPEND;

[Tom]

So it looks like you ran something like this:

%global Param1 Param2;
%global status result_1 ;

data UI_Result;
%let dt = &sysdate;

  format status $5.;
  format result_1 $200.;

  format parametri1 $20.;
  format parametri2 $20.;

  parametri1="&Param1.";
  parametri2="&Param2.";

  if strip(parametri1) in ('A','B','C','D') and parametri2 in (0,1,2,3,4) then do;
    status="OK";
    viesti=cat(catx(";","Test_available", compress(parametri1),compress(parametri2)));
  end;
  else do;
    status="Error";
    viesti=cat("Invalid input parameters ", compress(parametri1), " and ", compress(parametri2), ".");
  end;

  call symput ('status', status);
  call symput ('result_1', result_1 );
run;

%put &status.;
%put &viesti.;

This has nothing to do with any CSV file.

The main issue appears to be the %PUT statements at the end.

Your macro variables include semi-colons, so you need to add some macro quoting to them before using them like that.

So you could use 

%let status=%superq(status);
%let viesti=%superq(viesti);

Then try your %PUT statements.

Your existing code could be cleaned up quite a lot.  First do not embed a %LET statement in the middle of the definition of a data step. It will run BEFORE the data step so move it before the data step to avoid confusing the humans reading the code.

There is no need to attach formats to your character variables. Perhaps you are confused and think that the FORMAT statement defines variables. It will only the define the variable as a side effect if it happens to be the first place the variable is referenced.  There is no need to attach the formats. So just use the LENGTH statement to define the type and lengths of the variables.

Is PARAM2 a number or not?  You use it to create a character variable. Then you compare the character variable to a list of numeric values.   Which is it?  Assuming it is numeric then define the variable as numeric and remove the quotes. Otherwise add the quotes around the digits strings used with the IN operator.

Do you really want to remove the spaces from the values of PARAM1 when building VIESTI?  Otherwise why include the COMPRESS() function call?  If PARAM2 is numeric then COMPRESS() is inappropriate as it works on characters strings, not numbers.

Why are you using the ancient CALL SYMPUT() function instead of the current CALL SYMPUTX() function?  Did you really want the values of the macro variables to include spaces that are padded onto the end of data step variables?

%global Param1 Param2;
%global status result_1 ;
%let dt = &sysdate;

data UI_Result;
  length status $5 result_1 $200 parametri1 $20 parametri2 8;

  parametri1="&Param1.";
  parametri2=&Param2.;

  if strip(parametri1) in ('A','B','C','D') and parametri2 in (0,1,2,3,4) then do;
    status="OK";
    viesti=catx(";","Test_available",parametri1,parametri2);
  end;
  else do;
    status="Error";
    viesti=catx(' ',"Invalid input parameters",parametri1,"and",parametri2,".");
  end;

  call symputx('status', status);
  call symputx('result_1', result_1 );
run;

%let status=%superq(status);
%let viesti=%superq(viesti);

%put &status.;
%put &viesti.;

[PaigeMiller]

I think you need to take a step back and re-design this whole thing.

You want to write something to a CSV file. You don't need to turn that "something" into a macro variable.

Please explain the whole effort from start to finish, without macro variables or code being mentioned, just explain the project.

[Tom · Accepted solution]

Note you could to this without the data step if you wanted.  In some ways it will be easier: no need for those CAT() function calls for example, ability to add the macro quoting when setting the value.

%global status viesti;
options minoperator mindelimiter=' ';

%if (%quote(&param1) in A B C D) and (%quote(&param2) in 0 1 2 3 4) %then %do;
  %let status=OK;
  %let viesti=%quote(Test_available;&param1;&param2);
%end;
%else %do;
  %let status=Error;
  %let viesti=%quote(Invalid input parameters "&param1" and "&param2".);
%end;

%put &=status &=viesti;

[learn_SAS_23]

Thanks for Great inputs,am a beginner to SAS coding the tips are really helpful.

If status is ok , then i need to write the text in Viesti varaiable (i.e est_available;&param1;&param2) to a CSV file.
Can you share any inputs to write the text in macro variable (Viesti) to CSV file and that CSV file needs to append the data if the program runs on same day , and needs to create new file for next day

[Tom]

Explain what you mean about writing a CSV file. What CSV file?  What are the "columns" of the CSV file?  Do you need to include a header row?

Why are you putting semi-colons into the macro variable if you are writing to a Comma Separated Values file?  Do you mean you want to create a text files that has values delimited by semi-colon instead of a real CSV file?

[learn_SAS_23]

If the status is ok , then the file should be
header : execution_log;param1;param2
data would be as below
Test_available;A;0
Test_avilable;B;1
Do you want to create a text files that has values delimited by semi-colon instead of a real CSV file ?
Yes exactly , i want to create a file that

[Tom]

So it sounds like you want to APPEND a new line to an existing log file.

I would first create the file with just the header row.

execution_log;param1;param2

Then in you "job" write the status line to the end.

So if the file is called 'execution.log' the code might look like this to test the parameters and write a line to the log file.

data _null_;
  file 'execution_log' dsd dlm=';' mod ;
  length status param1 param2 $200;
  param1=symget('param1');
  param2=symget('param2');
  if (param1 in ('A' 'B' 'C' 'D'))  and (param2 in ('0' '1' '2' '3' '4') then 
     status='Test_available'
  ;
  else status='Invalid input parameter';
  put status param1 param2;
  call symputx('status',status);
run;

[learn_SAS_23]

yes, every day i need to create a new file ,
if the program runs more than once on a same day then the data needs to append to the file which runs on same day .

Let us say 07.09 it creates a new file execution_log_07_09_2021.csv on first run , on second run the data needs to append to the same file , for tomorrow run it needs to create a new file execution_log_08_09_2021.csv

[Tom]

First construct the filename.  I will use YMD order instead of the unworkable DMY order.  In addition to confusing half of your audience as to which part is the month and which is the day it also will not sort in chronological order. If you really have to use DMY or MDY order than use the appropriate FORMAT to generate that part of the filename.

%let datestamp=%sysfunc(translate(%sysfunc(today(),yymmdd10.),_,-));
%let filename=execution_log_&datestamp..csv ;

Then if you need a header record (why?) then if the file does not exist create it with the header record.

%if not %sysfunc(fileexist(&filename)) %then %do;
data _null_;
  file "&filename";
  put 'execution_log;param1;param2';
run;
%end;

Then just use that name in the code I posted before that appends the actual log of this run's status.

[learn_SAS_23]

Thanks a lot , it works perfectly as expected .!!