topic Re: Finding default values in a dataset given in macro variable default values in SAS Programming

Finding default values in a dataset given in macro variable default values

deleted_user — Sat, 29 May 2010 10:27:17 GMT

Hi All,
The below is my sample input dataset X.
And i have a list of default values which is in a macro variable
%let default_values=-999,-998,-997,999.
And i need the below output shown with variable and its associated default values only for numeric variables separated by comma.

Input Dataset:

Data X;
input x1 x2 x3 x4$;
cards;
123 234 345 A
-999 212 999 S
453 543 -998 Q
-998 768 -997 W
run;

Output:

Variable Default_Values
x1 -999,-998
x2
x3 999,-998,-997

Thanks
Sam...

Re: Finding default values in a dataset given in macro variable default values

Ksharp — Sat, 29 May 2010 13:20:08 GMT

I don't know whether the followsing code is what you need.
try it .
[pre]
%let default_values=-999,-998,-997,999;
Data X;
input x1 x2 x3 x4$;
cards;
123 234 345 A
-999 212 999 S
453 543 -998 Q
-998 768 -997 W
;
run;
data temp;
set x(drop=x4);
array x{3};
do i=1 to 3;
if x{i} in (&default_values) then continue;
else call missing(x{i});
end;
drop i;
run;
proc transpose data=temp out=data;
run;
proc print data=data noobs;
run;
[/pre]

Ksharp

Message was edited by: Ksharp

Re: Finding default values in a dataset given in macro variable default values

deleted_user — Mon, 31 May 2010 07:20:48 GMT

Thank you very much for the reply, but the output format which I need is it should have 2 columns one is column name and the second with default values separated by comma.
I dont prefer using transpose becas it generates all the obs into columns where in i have 1lakh obs in my dataset.

Regards
Sam

Re: Finding default values in a dataset given in macro variable default values

sbb — Mon, 31 May 2010 11:46:33 GMT

You can add a DATA step to concatenate non-missing variable values (generated by PROC TRANSPOSE) into a SAS CHARACTER type variable, using the CATX function and a DO/END code paragraph with an ARRAY to reference your numeric variables, as shown below:

LENGTH DefaultValues $500;
ARRAY ANUMVARS (*) COL: ;
DO I=1 TO DIM(ANUMVARS);
IF ANUMVARS(I) NE . THEN DefaultValues = CATX(',',DefaultValues,ANUMVARS(I));
END;

Scott Barry
SBBWorks, Inc.

Re: Finding default values in a dataset given in macro variable default values

Ksharp — Mon, 31 May 2010 13:05:21 GMT

Hi, if you do not have a lot of variables .

[pre]
%let default_values=-999,-998,-997,999;
Data X;
input x1 x2 x3 x4$;
cards;
123 234 345 A
-999 212 999 S
453 543 -998 Q
-998 768 -997 W
;
run;
data temp;
set x;
array x{*} _numeric_;
do i=1 to dim(x);
if x{i} in (&default_values) then continue;
else call missing(x{i});
end;
drop i;
run;
proc sql;
select x1,x2,x3
into :x_1 separated by ',' , :x_2 separated by ',' , :x_3 separated by ','
from temp ;
quit;
data result;
length variable $ 2
default_values $ 2000 ; *default_values's length can change to fit the number of your observations;
variable = 'x1' ;
default_values = compress(tranwrd("&x_1",'.,',' '),' .');
output;
variable = 'x2' ;
default_values = compress(tranwrd("&x_2",'.,',' '),' .');
output;
variable = 'x3' ;
default_values = compress(tranwrd("&x_3",'.,',' '),' .');
output;
run;
proc print noobs;
run;
[/pre]

Ksharp Message was edited by: Ksharp

Re: Finding default values in a dataset given in macro variable default values

deleted_user — Tue, 01 Jun 2010 05:35:31 GMT

Once again thank you for your qucik response but I need to find out the default values in a dataset which has around 400 variables and above 1 lakh obs...so please do consider it as a performance issue as I need to transpose a variable with huge obs..and few varaibles in my dataset has almost filled with all default values ....

Rgds
Sam...

Re: Finding default values in a dataset given in macro variable default values

Cynthia_sas — Tue, 01 Jun 2010 14:16:32 GMT

ODS CSV would create a comma separated file for you, once your dataset was in the correct structure.

But I'm confused. You said you have the list of default values in a macro variable. Then, you need to get rid of anything that is not one of these default values and your data is changing structure. When data changes structure, there is generally some transformation that needs to take place for every observation, possibly for every variable.

Possibly you could explain a bit more about your data going in and your data coming out -- such as why the data coming out has to be in such a particular format and/or whether there's any subsetting that can take place before the structure is transformed.

cynthia

Re: Finding default values in a dataset given in macro variable default values

Ksharp — Tue, 01 Jun 2010 15:08:12 GMT

Hi . sam
It should be a problem.
For your situation, It should be to use marco to recode the Proc sql .
But I do not know what your variables look like .

Re: Finding default values in a dataset given in macro variable default values

Ksharp — Wed, 02 Jun 2010 06:59:00 GMT

Hi, sam.
I think i got what you need. If all of cols of variables you analysised are numeric.
And thank sbb, you give a clever think.

[pre]
%let default_values=-999,-998,-997,999;
Data X;
input x1 x2 x3 x4$;
cards;
123 234 345 A
-999 212 999 S
453 543 -998 Q
-998 768 -997 W
;
run;
data temp;
set x;
array x{*} _numeric_;
do i=1 to dim(x);
if x{i} in (&default_values) then do;
varname=vname(x{i});
value=x{i};
output;
end;
else do;
varname=vname(x{i});
value=.;
output;
end;
end;
keep varname value;
run;
proc sort data=temp;
by varname;
run;
data tmp;
set temp;
by varname;
length variable $ 4 default_values $ 200;
retain variable default_values;
if first.varname then do;
variable = varname;
default_values = ' ';
end;

if not missing(value) then default_values=catx(',',default_values,value);

* this code is from sbb, thank sbb;
if last.varname then output;
keep variable default_values;
run;
proc print noobs;
run;
[/pre]

Ksharp Optimize code

Message was edited by: Ksharp Message was edited by: Ksharp

Re: Finding default values in a dataset given in macro variable default values

P_J — Fri, 04 Jun 2010 12:43:25 GMT

Another option for you,

%let default_values=-999,-998,-997,999;

Data X;
input x1 x2 x3 x4$;
cards;
123 234 345 A
-999 212 999 S
453 543 -998 Q
-998 768 -997 W
;
run;

proc transpose data = x out = x1;

run;

data x2( drop = COL:);
set x1(rename= _NAME_ = val);
array a _numeric_;
length str $50;
str = "";
do over a;
if a in (&default_values) then str = catx(',', str, a);

end;

run;

proc print data=x2;run;

Happy Friday,
PJ