BookmarkSubscribeRSS Feed
deleted_user
Not applicable
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...

9 REPLIES 9
Ksharp
Super 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
deleted_user
Not applicable
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
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
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.
Ksharp
Super 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
deleted_user
Not applicable
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...
Cynthia_sas
SAS Super FREQ
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
Ksharp
Super 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 .
Ksharp
Super 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
P_J
Calcite | Level 5 P_J
Calcite | Level 5
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

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 Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 9 replies
  • 1248 views
  • 0 likes
  • 5 in conversation