BookmarkSubscribeRSS Feed
turcay
Lapis Lazuli | Level 10

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;

Desired8.png

 

 

11 REPLIES 11
Reeza
Super User
This seems incredibly inefficient, what's the business case for such a method?
turcay
Lapis Lazuli | Level 10

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
Super User

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
Super User
Why can't you just use:

proc sql;
create table want as
select distinct quarter
from have
order by 1;
quit;
turcay
Lapis Lazuli | Level 10

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
Super User
Why macro variables? Why not create temp tables that are appended?
turcay
Lapis Lazuli | Level 10

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

 

Thank you.

Quentin
Super User

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
BASUG is hosting free webinars Next up: Jane Eslinger presenting PROC REPORT and the ODS EXCEL destination on Mar 27 at noon ET. Register now at the Boston Area SAS Users Group event page: https://www.basug.org/events.
turcay
Lapis Lazuli | Level 10

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
Super User
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.
BASUG is hosting free webinars Next up: Jane Eslinger presenting PROC REPORT and the ODS EXCEL destination on Mar 27 at noon ET. Register now at the Boston Area SAS Users Group event page: https://www.basug.org/events.
Reeza
Super User

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?

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
  • 11 replies
  • 4235 views
  • 1 like
  • 3 in conversation