topic Re: Sub data sets in SAS Data Management

Sub data sets

Rasheed — Fri, 28 Aug 2015 16:43:18 GMT

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

Re: Sub data sets

Reeza — Fri, 28 Aug 2015 17:15:20 GMT

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;

Re: Sub data sets

ballardw — Fri, 28 Aug 2015 17:19:24 GMT

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

Re: Sub data sets

Rasheed — Fri, 28 Aug 2015 17:55:20 GMT

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;

Re: Sub data sets

Reeza — Fri, 28 Aug 2015 20:21:58 GMT

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

Re: Sub data sets

ballardw — Fri, 28 Aug 2015 20:34:51 GMT

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

Call execute goes where the Put Names; statement is.

Re: Sub data sets

Rasheed — Sat, 29 Aug 2015 03:45:04 GMT

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.

Re: Sub data sets

LinusH — Sat, 29 Aug 2015 10:07:19 GMT

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;

Re: Sub data sets

Rasheed — Sun, 30 Aug 2015 08:09:22 GMT

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

Re: Sub data sets

Rasheed — Sun, 30 Aug 2015 08:22:40 GMT

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

Re: Sub data sets

Reeza — Sun, 30 Aug 2015 10:28:26 GMT

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;

Re: Sub data sets

Rasheed — Sun, 30 Aug 2015 11:24:52 GMT

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;

Re: Sub data sets

Reeza — Sun, 30 Aug 2015 11:53:12 GMT

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

Re: Sub data sets

Rasheed — Sun, 30 Aug 2015 12:15:35 GMT

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;

Re: Sub data sets

LinusH — Sun, 30 Aug 2015 12:24:32 GMT

CATS - RTM!

Re: Sub data sets

Rasheed — Sun, 30 Aug 2015 12:51:54 GMT

Sorry I didn't understand it

Re: Sub data sets

Patrick — Sun, 30 Aug 2015 13:15:19 GMT

It means: Use CATS() instead of CATX() SAS(R) 9.4 Functions and CALL Routines: Reference, Fourth Edition

Re: Sub data sets

Rasheed — Sun, 30 Aug 2015 13:22:42 GMT

When I used cats instead catx the problem is even bigger this time now it removes all spaces and produced

data CCD_1; setCD_11CD_12CD_13CD_14CD_15CD_16CD_17CD_18CD_19CD_110; rename COL1=C;

-------------------------------------------------------------------------------------------------------------

Re: Sub data sets

Reeza — Sun, 30 Aug 2015 15:48:13 GMT

try the concatenate function.

Look at all the options available in the CAT_ family as well and decide which will work for you.

Re: Sub data sets

LinusH — Sun, 30 Aug 2015 16:20:47 GMT

RTM = read the manual.

CATS is trimming all strings prior to concatenation. That's why you get the syntax error from your call execute.

Starting to feel that you want/need help to do the whole application. Perhaps time for you to try to solve your task, otherwise hire the muscle to do it.