DATA Step, Macro, Functions and more

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

Reply
N/A
Posts: 0

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

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...

Super User
Posts: 10,023

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

Posted in reply to deleted_user
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
N/A
Posts: 0

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

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
Super Contributor
Super Contributor
Posts: 3,174

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

Posted in reply to deleted_user
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.
Super User
Posts: 10,023

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

Posted in reply to deleted_user
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
N/A
Posts: 0

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

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...
SAS Super FREQ
Posts: 8,864

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

Posted in reply to deleted_user
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
Super User
Posts: 10,023

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

Posted in reply to deleted_user
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 .
Super User
Posts: 10,023

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

Posted in reply to deleted_user
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
Occasional Contributor P_J
Occasional Contributor
Posts: 10

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

Posted in reply to deleted_user
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 = COLSmiley Happy;
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
Ask a Question
Discussion stats
  • 9 replies
  • 200 views
  • 0 likes
  • 5 in conversation