Hi,
I am a novice to programming world need help.
I have two datasets:
dataset1: (with 620 unique STOREID and with onemore column for var2)
STOREID | Var2 |
1234 | xxxx |
567 | YYYY |
7332 | aaaa |
6664 | ttt |
87 | cdes |
20086 | aoia |
129743 | uytrewl |
dataset2: (with 34000 observation and 86 variables including STOREID)
Var1 | Var2 | Var3 | Var4 | STOREID | Var5 | Var6 | Var7 | Var8 | |
454 | 0 | a | 8 | 1234 | f9s97s | 9kj876 | 83 | ||
1234 | 087jn | 9kj876 | 83 | 8323 | |||||
16.987 | 1 | t | 8 | 1234 | 098ju5 | 9kj876 | 83 | 832341 | |
0.003 | 1 | y | 8 |
| 4h7s | 9kj876 | 831 | 3783232 | |
1098 | 2 | i | 8 |
| 9i86 | 9kj876 | 983 | 3832359 | |
987 | 4 | ac | 8 |
| 9juy | 9kj876 | 83 | 38323 | |
097 | 7 | cf | 8 | 90 | a764 | 9kj876 | 1 | 832325 | |
986 | 9 | fd | 8 | 90 | 6jh | 9kj876 | 3 | 3832132 |
I want to create another sas dataset from dataset2 by selecting all the observations from dataset2 for each of the STOREID present in dataset1. Can anybody help me with the code.(Note: I dont want to merge the given datsets, its more like index-match in excel, taking reference STOREID value from dataset1 and selecting all observations for the same reference STOREID value from dataset2 and creating a new datset with those observations)
"Note: I dont want to merge the given datsets," - then you cannot match the data. Data has to appear in the same place to be evaluated. Probably the simplest method is:
proc sql; create table want as select * from ds2 where storeid in (select storeid from ds1); quit;
That is however doing a sort of merged table in the background.
"Note: I dont want to merge the given datsets," - then you cannot match the data. Data has to appear in the same place to be evaluated. Probably the simplest method is:
proc sql; create table want as select * from ds2 where storeid in (select storeid from ds1); quit;
That is however doing a sort of merged table in the background.
@Ronin wrote:
Hey Hi, thanks for the solution. it worked. But this just provides me the log file which tells tells the info that table got created with 7095 rows and 86 observation..but din't get me a table with values to see. I want the table as a sas dataset(.sas7bdat) file. Can you help me with this.
If SAS tells you it created something, then it did, period. It may just be that your Enterprise Guide or SAS Studio is not set to automatically open results.
In case of other unclear issues, post the log (use the {i} button so that the log contents are not mangled by the forum software).
Regarding your request for code: if you provide example data in usable form (data steps with datalines, see my footnotes for creating such automatically and how to post the code), I can fiddle around with it and give you some examples.
The code I provided has a create table want as statement, which creates a dataset called want in the work library.
Probable methods:
1 & 2 are the most straight-forward ways, recognizable by any programmer who looks at it
3 & 4 need a little more expertise on the part of the programmer (especially 3), but give you better performance (no sorts needed).
OTOH with 34k and 600 observations, this will be done in fractions of a second anyway.
Edit: fixed a typo.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.