Hi,
How can i add the corresponding value from the right table ?
I have a dataset A with ID and DATE variables.
Dataset A :
ID DATE
1 201504
2 201001
and so on...
201504 refers to April, 2015, etc.
I want to add a variable "quantity" to this dataset A.
This variable is available in a list of datasets that all have the same name, except their date. So i have other datasets as :
- TABLE_201001
-TABLE_201002
...
-TABLE_201504
...
These datasets are constructed like this, e.g TABLE_201001 :
ID quantity date
1 445 201001
2 356 201001
...
See here the key merge is ID but also the information about the date.
How can i have sth like this in dataset A with quantity coming from the right table ?
ID DATE QUANTITY
1 201504 445 /*coming from dataset TABLE_201504 */
2 201001 356 /* coming from dataset TABLE_201001 */
I don't want to merge TABLE datasets as i have a lot of them and only showing here an exemple.
Thought of doing a loop with tables and if date values match then adding the value for the ID observation, else keep looking for other datasets.
Do all the TABLE_YYYYMM tables have the same structure?
Ok. See if you can use this as a template then
data a;
input ID DATE :yymmn6.;
format DATE yymmn6.;
datalines;
1 201504
2 201001
;
data TABLE_201001;
input ID quantity date :yymmn6.;
format date yymmn6.;
datalines;
1 445 201001
2 356 201001
;
data TABLE_201504;
input ID quantity date :yymmn6.;
format date yymmn6.;
datalines;
1 445 201504
2 356 201504
;
data table;
set table_:;
run;
proc sql;
create table want as
select a.*
, quantity
from a left join table t
on a.ID = t.ID
and a.date = t.date
;
quit;
How would add that value for a single value?
Can you show an example of what those tables look like and how that join would work for single table?
You'll need to join with multiple tables and exactly how can vary...multiple ways to do many things as usual.
@elsfy wrote:
Hi,
How can i add the corresponding value from the right table ?
I have a dataset A with ID and DATE variables.
Dataset A :
ID DATE
1 201504
2 201001
and so on...
201504 refers to April, 2015, etc.
I want to add a variable "quantity" to this dataset A.
This variable is available in a list of datasets that all have the same name, except their date. So i have other datasets as :
- TABLE_201001
-TABLE_201002
...
-TABLE_201504
...
These datasets are constructed like this, e.g TABLE_201001 :
ID quantity date
1 445 201001
2 356 201001
...
See here the key merge is ID but also the information about the date.
How can i have sth like this in dataset A with quantity coming from the right table ?
ID DATE QUANTITY
1 201504 445 /*coming from dataset TABLE_201504 */
2 201001 356 /* coming from dataset TABLE_201001 */
I don't want to merge TABLE datasets as i have a lot of them and only showing here an exemple.
Thought of doing a loop with tables and if date values match then adding the value for the ID observation, else keep looking for other datasets.
Do the TABLE_201001 tables have more than one record?
Is this supposed to match on another variable? (Not at all stated) If so state the variable , is that variable duplicated in any of the TABLE_201001 data sets?
Is the value in your data set A actually as SAS date value?? If so, show us what format is currently applied to the variable. If not you likely should create an actual SAS date value as that will allow many things that random number/character combinations impersonating dates won't let you do.
In any case you need to provide details about your existing data sets such as any other matching variables, variable types and possibly formats.
Here is a brief example of combining a couple data sets and pulling part of the data set name as a value in a combined data set.
data example_1; input id value; datalines; 1 23 2 34 ; data example_2; input id value; datalines; 1 14 2 77 ; data combined; set example_: indsname=setname; indicator = scan(setname,2,'_'); run;
The first two data steps just create data for an example. The third data set combined shows how to combine a lot of similarly named data sets with the : list builder. The : immediately after Example_ tells SAS that you want to use ALL of the data sets whose names start with Example_.
The INDSNAME option adds a temporary variable that holds the names of the data sets, in this case Setname, that will not be added to the data set. So you can use the variable Setname, which will have the library and data set name for parsing. In this case, similar to your Table_201001 sets, the _ separates the stem of the name, Table, from the indicator value 201001. So the Scan function can pull that from the Setname variable using the _ as a delimiter.
Then something like:
data control; input id indicator $; datalines; 1 3 1 2 1 1 2 2 3 2 ; proc sort data=control; by id indicator; run; proc sort data=combined; by id indicator; run; data want; merge control combined; by id indicator; run;
To add values.
Note: I purposely included values in the Control set that did not have match.
Note also that the Indicator variable in the Control set is Character. That is so the value will match the Indicator made in the Combined set. Which is why we need details of your variables.
If there is NOT a second variable in Control set to match on then this exercise may very well be a complete waste of time without considerably more actual example data as which values to match may not be obvious.
@ballardw I think this was merged or added to a different posts by accident?
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: