BookmarkSubscribeRSS Feed
Sepehrp
Fluorite | Level 6

Hello everyone, I have a data set with about 900 columns in it. I want to export this data set into access, since access cannot handle more than 255 variables, I want to split the data set into four data sets with less than 255 columns. I was wondering is there a way to select a range of columns in sas? Thanks!

7 REPLIES 7
pearsoninst
Pyrite | Level 9
Try this ..

data have;
input x1 x2 y1 y2 y3 ;
datalines;
1 2 5 8 10
3 4 7 9 12
;
run;
data  want1;
set have;
drop x1-x2;
run;
data want2;
set have;
drop y1-y3;
run;
Sepehrp
Fluorite | Level 6
thanks for your time 🙂
ArtC
Rhodochrosite | Level 12

You can also select columns using other naming shortcuts:

 

Select those variables starting with the prefix X

  keep x:;

 

select all numeric or all character variables

  keep _numeric_;

  keep _character_;

 

Select by naming variables with a common prefix and a numeric suffix

  keep month1 - month12;

 

 

ArtC
Rhodochrosite | Level 12

I couldn't help myself.  Smiley Happy

Here is a macro that will split the data set into a user specified number of data sets.

 

%macro split(dsn=, dsnroot=fred, splitcnt=3);
%local i j;
proc contents data=&dsn
              out=vlist(keep=name)
              noprint;
   run;

proc sql noprint;
   select name
      into :vars1 -
         from vlist;
   %let vcnt = &sqlobs;
   quit;

%* Nominal number of variables in each new dataset;
%let cnteach = %sysevalf(&vcnt/&splitcnt,ceil);

data
   %do i = 1 %to &splitcnt;
      &dsnroot&i(keep=
         %do j= %sysfunc(max(1,%eval((&i-1)*&cnteach+1))) %to %sysfunc(min(&vcnt,%eval((&i)*&cnteach)));
           &&vars&j
           %put &=i &=j;
         %end; )
   %end;
   ;
   set &dsn;
   run;
%mend split;

%split(dsn=sashelp.shoes, dsnroot=want, splitcnt=2)

Sepehrp
Fluorite | Level 6
Thank you so much for the effort you put on this 🙂
LinusH
Tourmaline | Level 20
How do you even handle so many columns? And how will the user's in Access use this data now that's even split into separate tables? Normalizing (probably some transposing included) is probably a better way to maintain and report on this data.
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
  • 7 replies
  • 2303 views
  • 4 likes
  • 5 in conversation