Convert values of a macro variable from numeric to text with "CALL SYMPUT"

[Abelp9]

​Hi, I'm new to SAS programming and I'm trying to store different values in different macro variables automatically with the following code:

data _null_;
	set TABLE_WITH_DAYS;
	call symput ('dia'||left(_n_),trim(day);
	call symput ('ndia',_n_);
run;

​With this code, what I do is store in macrovariables with different names (&dia1, &dia2, &dia3, &dia4...) the following values (20220101,20220102,20220103,20220104...). The problem is that it returns macros with numerical values and I want the days to be in string like this: ('20220101','20220102','20220103','20220104').

I have tried to do it this way but it doesn't work for me, would someone know where I should put the "put" function or how I should do it?

data _null_;
	set TABLE_WITH_DAYS;
	call symput ('dia'||left(_n_),strip(put(day, best32.)));
	call symput ('ndia',_n_);
run;

These are the macro variables that I generate with the first code, what I want is for it to return the values in string instead of numeric:

Thanks in advance.

[PaigeMiller]

Let us start with the discussion about how DATA values usually do not belong stored in macro variables. This is usually inefficient and causes beginners such as you to struggle creating the proper macro variables. So before I go ahead and straighten out your macro issue, please explain why you need data values such as these stored in macro variables. What exactly will you do with all of these macro variables once you have them??? Please be specific and detailed, as it is quite probable you don't need macro variables at all.

[Abelp9]

I have to extract data from a huge file, which contains a field called day, in that file there is data from 2016 to 2022 and it has about 20k lines per day (20k lines for day 20220101, 20k lines for 20220202.. .), I want to take out 160 days and I am creating these macros to put the extraction inside a loop and take out the 160 days directly.

This macro doesn't work for me either and I don't really understand why:

%macro string_days;
%do j=1 %to &ndia;
%let dia_&j = %str(%'&dia&j');
%put &dia_&j;
%end;
%mend string_days;
%string_days;

[PaigeMiller]

---

@Abelp9 wrote:

I have to extract data from a huge file, which contains a field called day, in that file there is data from 2016 to 2022 and it has about 20k lines per day (20k lines for day 20220101, 20k lines for 20220202.. .), I want to take out 160 days and I am creating these macros to put the extraction inside a loop and take out the 160 days directly

---

And then do what once you have extracted data for a 160 day period? Explain the big picture to me.

So, just to be clear, are these 160 days overlapping (first 160 days begins on 20220101, next 160 days begins on 20220102, and so on) or are they non overlapping (first 160 days begins on 20220101, next 160 days begins 160 days later, and so on)?

How is the field named DAY formatted, according to PROC CONTENTS? Is it numeric or character, according to PROC CONTENTS? Why do you think you need quotes around the macro variable value?

[Abelp9]

This is only part of a very long process to explain, it is a sales file, so there will be 20k lines for the day 20220101 in which they differ in other fields such as the name of the product, the same for the day 20220102 and so on. I extract a period of 160 days and there would be a table with about 3M records (160 x 20000).

I take the days field from a table that has this field in numeric format, and in the original sales table from which I extract the 160 records, this field is in string format, which is why I have to transform the string values

[PaigeMiller]

I have been through this with many people here, they are convinced that macros are the way to go and struggle to create the proper macro code, and then we convince them that you don't need macros and the coding is much easier (example: https://communities.sas.com/t5/New-SAS-User/Scan-a-list-of-datetime16/td-p/823286).

So please, for now, let's stop talking about macros. You have to answer my questions above. You did not tell me what you will do with these extracts of 160 days once you have them. You did not state if the 160 days are overlapping. What is the format of this DAY variable, according to PROC CONTENTS? Is this DAY variable numeric or character (text) according to PROC CONTENTS.

[Kurt_Bremser]

First of all, BEFORE you go ANY further, make data out of your dungheap.

SAS dates are stored in a very specific way which makes working with them extremely easy. The SAS way to store dates is as a numeric value which counts the days from 1960-01-01. A proper format makes the number human-readable.

Before you start working with your data, convert all values which are meant to represent dates to SAS dates. Finding a span of 160 days is then done in a single DO loop, and if those days are not strictly consecutive, but come from a list, store that list in a dataset and use it as a lookup.

Trying to do it with macro variables is, at best, inefficient.

In any way, prepare your data first so that your work becomes easy.

Once you have everything as SAS dates, and you have a lookup, extracting goes like this:

data want;
set all_sales;
if _n_ = 1
then do;
  declare hash days (dataset:"days");
  days.definekey("date");
  days.definedone();
end;
if days.check() = 0;
run;

It is assumed that the date variable is named "date" in both datasets.

[kleelasiva9]

data TABLE_WITH_DAYS;
input day;
cards;
20220101
20220102
20220103
20220104
;
run;

data _null_;
	set TABLE_WITH_DAYS;
	call symput ('dia'||left(put(_n_,best.)),quote(strip(put(day,best.))));
	call symput ('ndia',left(put(_n_,best.)));
run;

%put &=dia1 &=dia2 &=dia3 &=dia4 ;
/*DIA1="20220101" DIA2="20220102" DIA3="20220103" DIA4="20220104"*/

/*OR*/

proc sql;
select quote(strip(put(day,best.))) 
into :dia separated by ',' 
from TABLE_WITH_DAYS;
quit;

%put &=dia;
/*DIA="20220101","20220102","20220103","20220104"*/

[Abelp9]

Hello, thank you very much for your answer, if I want the values to be returned with single quotes instead of with double quotes, would I have to change the quote function?

[Tom]

The QUOTE() function has an optional second parameter that allows you to specify which quote character to use. 

QUOTE(string,"'")

If you want to make a quoted list of digit strings from your list of numbers you can do it in one macro variable. 160 digit strings of 8 digits each with quotes and separator will take 160*(8+2+1) = 1760 bytes.

So you could do this in the step that generates the macro variable.

data _null_;
  set TABLE_WITH_DAYS end=eof;
  length list $2000 ;
  retain list;
  list = catx(',',list,quote(cats(day),"'"));
  if eof then call symputx('daylist',list');
run;

Which you could then use to subset your dataset that has the character variable the with the "day" digit strings.

data want;
  set have ;
  where char_day in (&daylist);
run;