SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

Find and print first non-zero value from a dataset in columns

Reply
Occasional Contributor
Posts: 5

Find and print first non-zero value from a dataset in columns

 

I have a data set with ID in rows and months in columns, as the one shown below.

 

I want to create an auxiliary column that records the first value that is not zero of each line.

 

IDM1M2M3M4M5 Auxiliary column
100887 8
2777.. 7
300009 9
409998 9
511111 1
602211  

 

Currently l am using this code, but I haven't been able to get the results I am looking for. Any ideas?

data new_ops04;     
set new_ops03;

array MONTHS (24) M1-M24;

RETAIN AUXILIARY_COLUMN 0;
	do i=1 to 24;
	IF MONTHS(i) ne 0 and AUXILIARY_COLUMN = 0 THEN
               AUXILIARY_COLUMN = MONTHS(i);
	end;
		drop i;
run;

 

Thanks a lot!

PROC Star
Posts: 7,467

Re: Find and print first non-zero value from a dataset in columns

Posted in reply to pablorodriguez1
data new_ops04;     
  set new_ops03;

  array MONTHS (24) M1-M24;

  do _n_=1 to 24;
    IF MONTHS(_n_) gt 0 THEN do;
      AUXILIARY_COLUMN = MONTHS(_n_);
      leave;
    end;
  end;
run;
 

Art, CEO, AnalystFinder.com

 

Ask a Question
Discussion stats
  • 1 reply
  • 303 views
  • 2 likes
  • 2 in conversation