How to -> Append Macro Variable Values from One Dataset to Another Dataset

[turcay]

Hello everyone,

I’m trying to append values of my column on another dataset. I have dataset which includes thousand rows and columns. I prepared a sample dataset as below. Firstly, I want to get values of my column as macro variable then I want to add this values on another dataset. I tried on Want dataset but I couldn’t succeed. The main point is get values of column and append this values row by row. Could you help me, please ?

Data Have;
Length Variable $ 12 Value 8;
Infile datalines missover dlm=",";
Input Variable Value;
Datalines;
Q1,5
Q2,4
Q3,3
Q4,2
Q5,1
;
Run;
Proc SQL;
/*Create Table Have2 as --> Why this statement doesn't work in the macro variable */ 
Select Variable Into : list separated by " "
From Have;
QUIT;

%Put &list;

Data Want;
Set Have;
NewVariable=&list;
Run;

[Reeza]

This seems incredibly inefficient, what's the business case for such a method?

[turcay]

Actually, It is not related to the case, I just need it. The case includes Banking but for the Data Preparation I need it. What does "incredibly inefficient" mean. Is it possible or not ? Or you are thinking it is redundant ?

Thank you.

[Reeza]

Your requested solution is to create macro variables and append them one by one to a data step.

Here's how that process might work:

1. Create a macro variable with a list of values - if you can do this, it would easy to dump directly into a data set.

2. Determine number of values - COUNTW with %sysfunc or a data _null_ step

3. Create loop for macro variables to loop across each data step. This is the way, especially if you want all the interim data sets.

4. If first loop, create dataset and Add first variable. If not, To add next value would need to read full dataset, using the END option to indicate last record

5. Loop to top of macro loop until done.

An alternative would be to loop within a data step and use OUTPUT statements to explicitly output variables.

However, if you can do step 1, you can usually pull them directly into a data set. 

[Reeza]

Why can't you just use:

proc sql;
create table want as
select distinct quarter
from have
order by 1;
quit;

[turcay]

This is the worst side giving an sample dataset. I can understand you but please don't consider the sample dataset. As I said the main point is get values of column as macro and append this values row by row. I added the sample dataset to be able to see the method.

Thank you.

[Reeza]

Why macro variables? Why not create temp tables that are appended?

[turcay]

Okay, I want to learn both methods. Won't it be nice to learn a variety of methods ? 

Thank you.

[Quentin]

I'm not sure I understand what you're trying to do.  And I think there are probably better approaches. 

That said, maybe you're thinking something like:

33   data want;
34     set have;
35     NewVariable=scan("&list",_n_);
36     put (_all_)(=);
37   run;

Variable=Q1 Value=5 NewVariable=Q1
Variable=Q2 Value=4 NewVariable=Q2
Variable=Q3 Value=3 NewVariable=Q3
Variable=Q4 Value=2 NewVariable=Q4
Variable=Q5 Value=1 NewVariable=Q5
NOTE: There were 5 observations read from the data set WORK.HAVE.
NOTE: The data set WORK.WANT has 5 observations and 3 variables.

If the list is too big and you hit problems with scan, you could use a temporary array to hold the values.

But why would this be better than just merging on a column?  Note that if you want to just smush one variable onto a table ("smush" is my term for a merge with no by-variable matching), you can do that:

53   data want;
54     merge have
55           have(keep=variable rename=(variable=NewVariable))
56     ;
57     *no by;
58     put (_all_)(=);
59   run;

Variable=Q1 Value=5 NewVariable=Q1
Variable=Q2 Value=4 NewVariable=Q2
Variable=Q3 Value=3 NewVariable=Q3
Variable=Q4 Value=2 NewVariable=Q4
Variable=Q5 Value=1 NewVariable=Q5
NOTE: There were 5 observations read from the data set WORK.HAVE.
NOTE: There were 5 observations read from the data set WORK.HAVE.
NOTE: The data set WORK.WANT has 5 observations and 3 variables

[turcay]

Thank you for your help. Your approaches can be better for me but I couldn't understand the

put (_all_)(=);

 statement.

I searched on communites and I found some examples like this one -> https://communities.sas.com/t5/Base-SAS-Programming/put-all/td-p/54482. At the following example;

data _null_;
  file 'c:\temp\test.csv' dsd delimiter=";";
  set sashelp.class;
  put (_all_)(+0);
run;

It puts semicolon between all of the values, isn't it ? On your example, what "put (_all)(=)" statement do ? Could you explain briefly, please ?

Thank you.

[Quentin]

When you have a FILE statement in your step, the PUT statement writes to the file (your example). Without a FILE statement the PUT statement writes to the log (my example). This is often useful for debugging, or in this case just displaying values. It is not a part of the solution.

[Reeza]

So how does your actual requirements vary that SELECT DISTINCT doesn't work. 

That's a single way to get a list of unique values out, not the only way, proc freq is another way.

In general, I macro's are only useful when:

• The routine is used more than once.

• The routine depends on a value of a variable or parameter.

• The routine requires programming logic that cannot be included in a DATA step.

Is there some part of your requirements where any of the above become valid?