BookmarkSubscribeRSS Feed
Rasheed
Calcite | Level 5

I have data sets like

CD_11  CD_12 ........CD_110

CD_21  CD_22.........CD_210

................................................

CD_111  CD_112.........CD_1110

Now I want merge data sets CD_11 CD_12  CD_13 ..... CD_110  into data set CCD_1

similarly CD_21 CD_22  CD_23..... CD_210  into data set CCD_2

..............................................................................

and CD_111 CD_112  CD_113 ..... CD_1110  into data set CCD_11

For this I am using following sas command

data _null_;

     do j=1 to 11;            

     call execute(cats('data CCD_',put(j,8.),'; set CD_',put(j,8.),'1:CD_',put(j,8.),'10; rename COL1=A;' ));

      end;         

     run;                                                                                                                                                                                                                                                           

But it is not producing desired output

Pls help

20 REPLIES 20
Reeza
Super User

Are you appending data sets?

You have to make sure the code generated is valid so it obviously isn't for some reason. 

I think you need a - instead of a colon. I'm also not sure SAS will properly identify the datasets so you may want to add the INDSNAME option so you can identify the source data.

data test;

     do j=1 to 11;

str= cats('data CCD_',put(j,8.),'; set CD_',put(j,8.),'1-CD_',put(j,8.),'10 indsname=source; rename COL1=A; dname=source;' );

output;

      end;        

     run;

data CCD_1; set CD_11-CD_110 indsname=source; rename COL1=A; dname=source;

data CCD_2; set CD_21-CD_210 indsname=source; rename COL1=A; dname=source;

data CCD_3; set CD_31-CD_310 indsname=source; rename COL1=A; dname=source;

data CCD_4; set CD_41-CD_410 indsname=source; rename COL1=A; dname=source;

data CCD_5; set CD_51-CD_510 indsname=source; rename COL1=A; dname=source;

data CCD_6; set CD_61-CD_610 indsname=source; rename COL1=A; dname=source;

data CCD_7; set CD_71-CD_710 indsname=source; rename COL1=A; dname=source;

data CCD_8; set CD_81-CD_810 indsname=source; rename COL1=A; dname=source;

data CCD_9; set CD_91-CD_910 indsname=source; rename COL1=A; dname=source;

data CCD_10; set CD_101-CD_1010 indsname=source; rename COL1=A; dname=source;

data CCD_11; set CD_111-CD_1110 indsname=source; rename COL1=A; dname=source;

ballardw
Super User

It is helpful to describe how the output is not desired.

I suspect if you look at the log you would see a data set built that references more sets read than you expected.

Same as last time: PUT the string you are sending to Call execute.

You are generating things like

data CCD_1; set CD_11:CD_110; rename COL1=A;

Good idea to explicitly include the Run;

Set CD_11: is going to include CD_11, CD_110 CD_111 CD_112 etc.

The list modifier : treats the part to the left as a stem and anything starting with that stem is considered matching.

Explicitly build the list of names as a single variable before the call execute, it'll be easier.

data _null_;
     length names $ 200;
     do j=1 to 11;
       names='';
       do i = 1 to 10;
         names = catx(' ',names,cats("CD_",J,I));
      end;
      put names;
     end;
run;

Use that names variable in your call execute

Rasheed
Calcite | Level 5

Thanks ballardw

I have successfully generated variables names

CD_11 CD_12 CD_13 CD_14 CD_15 CD_16 CD_17 CD_18 CD_19 CD_110

CD_21 CD_22 CD_23 CD_24 CD_25 CD_26 CD_27 CD_28 CD_29 CD_210

CD_31 CD_32 CD_33 CD_34 CD_35 CD_36 CD_37 CD_38 CD_39 CD_310

CD_41 CD_42 CD_43 CD_44 CD_45 CD_46 CD_47 CD_48 CD_49 CD_410

CD_51 CD_52 CD_53 CD_54 CD_55 CD_56 CD_57 CD_58 CD_59 CD_510

CD_61 CD_62 CD_63 CD_64 CD_65 CD_66 CD_67 CD_68 CD_69 CD_610

CD_71 CD_72 CD_73 CD_74 CD_75 CD_76 CD_77 CD_78 CD_79 CD_710

CD_81 CD_82 CD_83 CD_84 CD_85 CD_86 CD_87 CD_88 CD_89 CD_810

CD_91 CD_92 CD_93 CD_94 CD_95 CD_96 CD_97 CD_98 CD_99 CD_910

CD_101 CD_102 CD_103 CD_104 CD_105 CD_106 CD_107 CD_108 CD_109 CD_1010

CD_111 CD_112 CD_113 CD_114 CD_115 CD_116 CD_117 CD_118 CD_119 CD_1110

But I am confused where should I use names in my following code to get my output

data _null_;

     do j=1 to 11;           

     call execute(cats('data CCD_',put(j,8.),'; set CD_',put(j,8.),'1:CD_',put(j,8.),'10; rename COL1=A;' ));

      end;        

   run;    

Reeza
Super User

work backwards. What do you want your code to look like. What variables do you need to support that

ballardw
Super User

The Names variable goes after set ' and before '; rename col1=A;'

Call execute goes where the Put Names; statement is.

Rasheed
Calcite | Level 5

I am trying this but it gives error

data _null_;                                                                                                                                                                                                                                                   

     do j=1 to 10;                                                                                                                                                                                                                                            

          call execute(cats('data CCD_',put(j,8.),'; set names ; rename COL1=C;' ));                                                                                                                                                                

       end;                                                                                                                                                                                                                                                    

run;

Log error

1    + data CCD_1; set names ; rename COL1=C;

ERROR: File WORK.NAMES.DATA does not exist.

LinusH
Tourmaline | Level 20

I'm confused on what you actually have, and how you store it, or is it a solid naming convention?

So you have data sets and variables within them with the same names?

You say that you have "generated variable names", meaning what? How and where do you store those names?

In your latest code you have "hard coded" the data set name "name"....?

Then, I would use proc append instead, it's more efficient than data - set.

Why don't use dictionary.tables to build your call executes?

data _null_;

     set sashelp.vstable;

     where libname = 'WORK' and substr(memname,1,2) = 'CD';

     call execute(cats('proc append data='libname','.',memname,'(rename=(col1=a)) base=work.ccdwhatever; run;'));

run;

Data never sleeps
Rasheed
Calcite | Level 5

My problem is not solved yet please guide me towards its solution

Rasheed
Calcite | Level 5

Dear ballardw

I would be realy gratefull to you if could write complete statment of call execute inculding names variables becuase i tried it multiple times but getting errors

pls

Reeza
Super User

You didn't listen to his instructions though. Generate your string first, then build the call execute.

data want;

     length names $ 200;

     do j=1 to 11;

       names='';

       do i = 1 to 10;

         names = catx(' ',names,cats("CD_",J,I));

      end;

     

     

      str=cats('data CCD_',put(j,8.),'; set ', names ,'; rename COL1=C;');

      put str;

     end;

run;

Rasheed
Calcite | Level 5

When I run you above menetion code

and try to print data set CCD_1 the log says that

ERROR: File WORK.CCD_1.DATA does not exist.

data want;

     length names $ 200;

     do j=1 to 11;

       names='';

       do i = 1 to 10;

         names = catx(' ',names,cats("CD_",J,I));

      end;

    

    

      str=cats('data CCD_',put(j,8.),'; set ', names ,'; rename COL1=C;');

      put str;

     end;

run;

Proc print data=CCD_1;                                                                                                                                                                                                                                         

run;                                                                                                                                                                                                                                                           

Reeza
Super User

my code didnt include the call execute, add one in.

Rasheed
Calcite | Level 5

now it working fine but only one error of space between set and CD_11

log error is

data CCD_1; setCD_11 CD_12 CD_13 CD_14 CD_15 CD_16 CD_17 CD_18 CD_19 CD_110; rename

I am using following sas code

data want;                    

     length names $ 200;                                                                                                                                  do j=1 to 11;                                                                                                                                        names='';                                                                                                                                            do i = 1 to 10;

         names = catx(' ',names,cats("CD_",J,I));

      end;

     call execute(cats('data CCD_',put(j,8.),'; set ', names ,'; rename COL1=C;'));

     end;                                                                                                                                               run;                                                                                                                                                                                                                                                           

LinusH
Tourmaline | Level 20

CATS - RTM!

Data never sleeps

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 connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 20 replies
  • 2017 views
  • 1 like
  • 5 in conversation