topic Re: Hot to get required output. in SAS Programming

Hot to get required output.

Aayushi_17 — Tue, 16 Jul 2019 04:37:45 GMT

how to get the required output from the condition if check1-check24 = '1' then TERM=(frac1_ to frac24_). Using macro.

Check1_	Check2_	Check3_
1		1
1	1	1
1	1	1
1		1
1	1
1	1
1

frac1_	frac2_	frac3_
Extreme		Good
Good	Extreme	Good
Average	Not Good	Good
Not Good		Good
Neat	Neat
Vba	Neat
Average

Required output.

TERM

Extreme

Good

Average

Not Good

Neat

Vba

Average

Extreme

Not Good

Neat

Good

Re: Hot to get required output.

PeterClemmensen — Tue, 16 Jul 2019 05:13:58 GMT

I don't understand this. So if all your check variables are equal to '1', then what?

Re: Hot to get required output.

Aayushi_17 — Tue, 16 Jul 2019 05:17:19 GMT

Then I have get the required output for term..it's s condition like if
check1="1" then term=frac1_;

Re: Hot to get required output.

andreas_lds — Tue, 16 Jul 2019 06:17:43 GMT

@Aayushi_17 wrote:
Then I have get the required output for term..it's s condition like if
check1="1" then term=frac1_;

Sorry, i don't understand what you have and what you want. From the tables you have posted, the result seems to be a list of the non-null values of you frac-table, there seems to be no need to use the check-table at all. So ignoring "check", you should: use a data-step to transpose "frac" so that you have a table of frac-number and the value (skip missing values). then sort by frac-number -> job done.

Note: If you want code, you have to post data in usable form: a data step using datalines statement.

Re: Hot to get required output.

Kurt_Bremser — Tue, 16 Jul 2019 08:01:09 GMT

No macro needed. You want to stack the columns, and to do that dynamically, use sashelp.vcolumn as a source:

data have;
infile datalines dlm=',' dsd truncover;
input (frac1_ frac2_ frac3_) ($);
datalines;
Extreme,,Good
Good,Extreme,Good
Average,Not Good,Good
Not Good,,Good
Neat,Neat
Vba,Neat
Average
;

data _null_;
set sashelp.vcolumn (
  where=(libname = 'WORK' and memname = 'HAVE')
) end=eof;
if _n_ = 1 then call execute('data want (keep=term where=(term ne " ")); set');
call execute('
  have (rename=(' !! strip(name) !! '=term))
');
if eof then call execute('; run;');
run;

proc print data=want noobs;
run;

Result:

term

Extreme 
Good    
Average 
Not Good
Neat    
Vba     
Average 
Extreme 
Not Good
Neat    
Neat    
Good    
Good    
Good    
Good

Re: Hot to get required output.

Ksharp — Tue, 16 Jul 2019 11:49:17 GMT

Do you have IML ?

data have;
infile datalines dlm=',' dsd truncover;
input (frac1_ frac2_ frac3_) ($);
datalines;
Extreme,,Good
Good,Extreme,Good
Average,Not Good,Good
Not Good,,Good
Neat,Neat
Vba,Neat
Average
;

data temp;
 set have;
 array x{*} $ frac: ;
 do i=1 to dim(x);
   if not missing(x{i}) then do;name=x{i};output;end;
 end;
 keep i name;
run;
proc sort data=temp out=want;
 by i;
run;

Re: Hot to get required output.

gamotte — Tue, 16 Jul 2019 12:35:12 GMT

Can we be assured that the initial order will be preserved for a given value of i ? Is that a documented feature ?

Re: Hot to get required output.

Ksharp — Wed, 17 Jul 2019 11:06:12 GMT

Sorry. I don't understand you .

But as from OP' data, These two tables is perfectly matched ,

therefore I assume " be assured that the initial order will be preserved".

Re: Hot to get required output.

gamotte — Wed, 17 Jul 2019 12:34:54 GMT

Sorry for not being clear. English is not my first language.

I meant that you do not have to do something like :

data temp;
    set have;
    array x{*} $ frac: ;

    n=_N_; /* we keep track of the initial order */

    do i=1 to dim(x);
        if not missing(x{i}) then do;name=x{i};output;end;
    end;
    keep i n name;
run;

proc sort data=temp out=want;
    by i n;
run;

to ensure that for i=1, the order of values is the same as in column frac1_ of the initial dataset.

That is, the default sort algorithm for proc sort ensures that observations with the same value for the

sort criterion wil appear in the same order than before the sort.

Re: Hot to get required output.

Ksharp — Wed, 17 Jul 2019 12:42:28 GMT

Ha. That is what PROC SORT will consider (keep original order).

That is a big thing I love SAS.

Unless you used option "noequals" .

proc sort data=have noequals;

Re: Hot to get required output.

gamotte — Wed, 17 Jul 2019 12:44:49 GMT

Thanks !