@David_Billa wrote:
Thanks for the suggestion. Will I get any error, if any of the datasets which I listed under Filelist macro variable or in HAVE dataset is not created in the program? If it throws an error, how to tackle this?
What does that mean?
If the table exists it will work. Not sure how it could know WHEN it was created.
Are you asking how to check if the table exists? We showed that code in other posts in one of your threads. There is an EXIST() function to check for the existence of a SAS dataset.
@Tom I just ran your code and it is not producing the desired Output. Output of your code is,
Run_ID | Company_code | Datasource | Table_name | table_count |
12345 | ABC | Database | Insurancg | 89 |
12345 | ABD | Database | Insurancg | 89 |
12345 | ABE | Database | Insurancg | 89 |
12345 | ABF | Database | Insurancg | 89 |
12346 | DEF | Excel | Insuranch | 90 |
data details; infile datalines truncover; input Run_ID :5. Company_code :$3. Datasource :$10. Table_name :$10. table_count : 2.; datalines; 12345 ABC Database 12345 ABD Database 12345 ABE Database 12345 ABF Database 12346 DEF Excel ; data insurance; input Company_code :$5. Table_name :$10. Table_count :2. Run_ID :5.; datalines; ABC Insurance 87 12345 ; data insurancf; input Company_code :$5. Table_name :$10. Table_count :2. Run_ID :5.; datalines; ABD Insurancf 88 12345 ; data insurancg; input Company_code :$5. Table_name :$10. Table_count :2. Run_ID :5.; datalines; ABE Insurancg 89 12345 ; data insuranch; input Company_code :$5. Table_name :$10. Table_count :2. Run_ID :5.; datalines; DEF Insuranch 90 12346 ; %macro update(table); proc sql; update details a set table_name = (select table_name from &table. b where a.run_id=b.run_id), table_count = (select table_count from &table. b where a.run_id=b.run_id) where a.run_id in (select b.run_id from &table. as b) ; quit; %mend; %let filelist=insurance insurancf insurancg insuranch ; data _null_; length tablename $32 ; do i=1 to countw("&filelist",' '); tablename=scan("&filelist",i,' '); call execute(cats('%nrstr(%update(',tablename,'))')); end; run;
Desired Output is,
Run_ID Company_code Datasource Table_name table_count
12345 ABC Database Insurance 87
12345 ABD Database Insurancf 88
12345 ABE Database Insurancg 89
12345 ABF Database
12346 DEF Excel Insuranch 90
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.