12-16-2015 05:48 PM - edited 12-16-2015 05:49 PM
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;
12-16-2015 06:06 PM
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 ?
12-17-2015 10:27 AM
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.
12-16-2015 06:17 PM - edited 12-16-2015 06:20 PM
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.
12-16-2015 09:03 PM
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
12-17-2015 04:07 AM
Thank you for your help. Your approaches can be better for me but I couldn't understand the
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 ?
12-17-2015 08:42 AM
12-16-2015 06:47 PM
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?