topic Re: Pick Unique records in SAS Programming

Pick Unique records

shiv999 — Sun, 13 Dec 2020 05:42:17 GMT

Hi , I have a dataset as shown below. I need to only pick the values like 2081 2082 2084 and 2084 into a 4 macro variable and ignore if 0000. Can you please suggest

Column1 | column2 | column3 | Column4
0000 | 0000 | 0000 | 2084
2081 | 0000 | 0000 | 0000
0000 | 2082 | 2083 | 0000

Re: Pick Unique records

ChrisNZ — Sun, 13 Dec 2020 07:17:53 GMT

There's always 4 variables, 3 rows, and 4 values?

Re: Pick Unique records

Kurt_Bremser — Sun, 13 Dec 2020 07:30:27 GMT

What will you be doing with those macro variables later on?

Re: Pick Unique records

shiv999 — Sun, 13 Dec 2020 07:31:05 GMT

Rows can be between 1 to 4 it depends

Re: Pick Unique records

shiv999 — Sun, 13 Dec 2020 07:38:21 GMT

The given dataset createf in a macro ,Based on the macro parameters.these dataset rows would be between 1 to 4 depend on the data. Now once I get the unique values I will pass the values into below code using a macro variable

Re: Pick Unique records

Kurt_Bremser — Sun, 13 Dec 2020 07:50:53 GMT

And what will that below code do?

I ask because in 90% (if not 100%) of cases it is not necessary to create a list of macro variables, but instead the code can be created (or called) directly from the dataset.

Re: Pick Unique records

shiv999 — Sun, 13 Dec 2020 08:45:35 GMT

Yes, I understand but existing code already use the macro variable . So I doesn't want to change the approach . Is there a way we do in macro variable?

Re: Pick Unique records

Kurt_Bremser — Sun, 13 Dec 2020 09:49:49 GMT

data _null_;
set have;
retain counter 0;
array col {*} column:;
do i = 1 to dim(col);
  if col{i} ne '0000'
  then do;
    counter +1;
    call symputx(cats('mvar',counter),col{i});
  end;
end;
run;

Mind that perpetuating a bad code structure by adding even more bad code to it is always a bad idea. Improving suboptimal code is a worthy investment into your own future.

Re: Pick Unique records

Ksharp — Sun, 13 Dec 2020 12:03:52 GMT

data have;
infile cards dlm='|';
input Column1- Column4;
cards;
0000 | 0000 | 0000 | 2084
2081 | 0000 | 0000 | 0000
0000 | 2082 | 2083 | 0000
;

proc sql;
select max(Column1),max(Column2),max(Column3),max(Column4)
  into : c1,: c2,: c3,: c4
   from have;
quit;

%put _user_ ;

Re: Pick Unique records

shiv999 — Mon, 14 Dec 2020 10:31:18 GMT

Thanks

Re: Pick Unique records

shiv999 — Mon, 14 Dec 2020 10:31:59 GMT

Thanks it's very awesome