topic how to resize datasets in SAS Programming

how to resize datasets

noda6003 — Wed, 13 Mar 2019 13:40:37 GMT

How to resize sas datasets.

I found a program in sas but it gives errors. one of the error is

http://support.sas.com/resources/papers/proceedings13/206P-2013.pdf

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

May be i am missing something. Any one knows program which does the resizing. please let me know

%macro chglen (inp=sl);

	proc contents data = &inp. noprint
		out = charvar (where =(type=2)
		keep = memname name type length label);
	run;

	data charvar2;
		length name1 name2 name3 name4 name5 $100;
		set charvar;
		name1 = trim(name)||'1=length('||trim(name)||')';
		name2 = 'max('||trim(name)||'1) as '||trim(name);
		name3 = trim(name)||'x = '||trim(name);
		name4 = trim(name)||' = '||trim(name)||'x';
		name5 = trim(name)||' $'||strip(put(length, best.));
	run;

	proc sql noprint;
		select trim(name1) into: newvar separated by '; '
			from charvar2;
		select trim(name2) into: maxvar separated by ', '
			from charvar2;
		select trim(name3) into: tname separated by '; '
			from charvar2;
		select trim(name4) into: tnamex separated by '; '
			from charvar2;
		select trim(name) into: cvar separated by ' '
			from charvar2;
		select trim(name)||'x' into: cvarx separated by ' '
			from charvar2;
		select trim(name5) into: cvarlen separated by ' '
			from charvar2;
	quit;

	data varlen;
		set &inp.;
		&newvar;
	run;

	proc sql noprint;
		create table maxx as
			select &maxvar
				from varlen;
	quit;

	data maxx_t2;
		length name6 $100;
		set maxx_t;

		if col1<1 then
			col1=1;
		name6 = trim(name)||' $'||strip(put(col1, best.));
	run;

	proc sql noprint;
		select strip(name6) into: newlen separated by ' '
			from maxx_t2;
	quit;

	data temp (drop = &cvar. );
		length &cvarlen;
		set &inp.;
		&tname.;
	run;

	data newone (drop= &cvarx.);
		length &newlen.;
		set temp;
		&tnamex.;
	run;

%mend chglen;

%chglen;

Re: how to resize datasets

gamotte — Wed, 13 Mar 2019 14:11:08 GMT

Hello,

The message in the log is quite clear : you refer to a dataset MAXX_T which doesn't exist.

The problem occurs in the data step that creates maxx_t2 :

data maxx_t2;
		length name6 $100;
		set maxx_t; /* HERE ! */
...
run;

The preceeding proc sql cretaes a dataset named MAXX. Maybe it is a typo and it should be named MAXX_T.

Re: how to resize datasets

noda6003 — Wed, 13 Mar 2019 14:49:24 GMT

It still gives errors

NOTE: Line generated by the macro variable "NEWLEN".
1 . $1
-
22
200

ERROR 22-322: Syntax error, expecting one of the following: a name, ;, DEFAULT, _ALL_, _CHARACTER_, _CHAR_, _NUMERIC_.

ERROR 200-322: The symbol is not recognized and will be ignored.

NOTE: The SAS System stopped processing this step because of errors.

Re: how to resize datasets

DaveHorne — Wed, 13 Mar 2019 15:02:38 GMT

I've had good results using the %chg_length macro documented in the sample library: http://support.sas.com/kb/48/638.html

I use it most often when extracting data from a third party database. It can make a huge performance benefit when you don't need to drag around large character variables.

Re: how to resize datasets

ballardw — Wed, 13 Mar 2019 15:08:10 GMT

What do you actually mean by "resize"?

You may want to consider learning to use the various CAT functions instead of the || operator. You can avoid most of the Trim() calls using CATS:

data charvar2;
   length name1 name2 name3 name4 name5 $100;
   set charvar;
   name1 = cats(name),'1=length(',name),')');
   name2 = cats('max(',name,'1) as ',name);
   name5 = cats('name,' $',length);
run;

CATS, CATT , CATX, CATQ and CAT all will automatically use the best. format to convert numeric values.

Re: how to resize datasets

gamotte — Wed, 13 Mar 2019 16:36:29 GMT

This program seems overly complicated and i can't really figure out what it does by just reading it.

If the goal is just to modify the length of character columns to the minimal length that avoids truncation,

you can try the following macro :

%macro resize(lib=sashelp, mem=class, out=&mem.);

    proc sql noprint;
        /* We get the list of charcter columns */
        SELECT NAME
        INTO :CHARCOLS SEPARATED BY ' '
        FROM dictionary.columns
        WHERE LIBNAME=upcase("&lib.") AND MEMNAME=upcase("&mem.")
        AND upcase(TYPE)="CHAR";

        /* For each char column we get the minimum length that avoids truncation */
        /* Ex: "var1 $5. var2 $7. ..."                                           */
        %let nchars=%sysfunc(countw(&CHARCOLS));

        SELECT DISTINCT cat(
        %do i=1 %to &nchars.;
            %let col=%scan(&CHARCOLS.,&i.,' ');
            " &col. $",max(length(&col.)),".",
        %end;
        "") /* "" for the last comma */
        INTO :lengths SEPARATED BY ' '
        FROM &lib..&mem.;
    quit;

    /* New dataset with resized char columns */
    data &out.;
        length &lengths.;
        set &lib..&mem.;

    run;

%mend resize;

%resize;

Re: how to resize datasets

ErikLund_Jensen — Wed, 13 Mar 2019 19:20:09 GMT

Hi @noda6003

Long character variables has always been a problem i SAS, and setting new lengths based on an analysis of actual data is dangerous, because next day's actual data might be longer. Some variables have content with a defined max. length, like a postal code or social security number, and for these the length can be set safely.

But give ample space to varying-length text, e.g. use 200 if actual data says 157. Consider compressing your SAS data with compress=char, if your concern is disk space.

An easy way to find the lengths in actual data is to export the troublesome variables to csv and import them to a work data set:

data have;
	length name1 name2 name3 $1024;
	name1 = 'Tom'; name2 = 'W.'; name3 = 'Jones';
run;

filename csvfile "%sysfunc(getoption(work))\w.csv";
proc export data=have outfile=csvfile dbms=csv replace;
run;
proc import datafile=csvfile out=want dbms=csv replace;
run;

Re: how to resize datasets

SASKiwi — Wed, 13 Mar 2019 19:25:18 GMT

What is the purpose of resizing your SAS datasets? If it is just to save space then it is a whole lot easier to use the SAS option COMPRESS = YES or COMPRESS = BINARY. We have the compression option set permanently to BINARY on our SAS installations and it works extremely well.