Please help me to get rid of the data step “data have; set have; if _n_>1; run;” in my code

[Linlin]

Hi All,

How can I get the same result without the data step “data have;  set have;  if _n_>1; run;”?

Thanks - Linlin

data have;

input (ABC F1 F2 FDG)($);

cards;

ID Name Sex Country

1 ABC M IND

2 BCD F USA

3 CDE M GER

4 DGE M UK

;

proc transpose data=have(obs=1) out=temp;

var _all_;

run;

proc sql ;

   select catx('=',_name_,col1)

     into :rename separated by ' '

        from temp;

quit;

/* please help me to get rid of this data step */

data have;  set have;  if _n_>1; run;

proc datasets nolist lib=work;

  modify have ;

  rename &rename;

  run;

quit;

proc print data=have;run;

                               Obs    ID    Name    Sex    Country

                                1     1     ABC      M       IND

                                2     2     BCD      F       USA

                                3     3     CDE      M       GER

                                4     4     DGE      M       UK

[Astounding]

Linlin,

I think the simplest approach would be to create 2 data sets instead of 1 originally:

data have1 have2;

   input ...;

   if _n_=1 then output have1;

   else output have2;

   cards;

[Linlin]

Hi Astounding,

I need to let you know that I am your sister, not your brother.:smileylaugh:

Thank you. I should not use proc datasets at all.

data have;

input (ABC F1 F2 FDG)($);

cards;

ID Name Sex Country

1 ABC M IND

2 BCD F USA

3 CDE M GER

4 DGE M UK

;

proc transpose data=have(obs=1) out=temp;

var _all_;

run;

proc sql ;

   select catx('=',_name_,col1)

     into :rename separated by ' '

        from temp;

quit;

data have;

  set have(firstobs=2  rename=(&rename));

run;

proc print;run;

updated after Astounding's comments.  Thank you!

[Astounding]

Sister,

If you're willing to go with the DATA step, here's a slight improvement:

data have (rename=(&rename));

   set have (firstobs=2);

run;

Good luck.

[chang_y_chung_hotmail_com]

I would prefer running a proc datasets to running a whole data step just to rename variables. In any case, the key here is to generate the rename list automatically. Here is another way. Just make sure that you give long enough length in the initial value of the retained variable in the data _null_ step.  hope this helps a bit.

data names have;
   input (a b c d) (:$8.);
   if _n_ = 1 then output names;
   else output have;
cards;
ID Name Sex Country
1 ABC M IND
2 BCD F USA
3 CDE M GER
4 DGE M UK
;
run;

proc transpose data=names
   out=names(rename=(_name_=old col1=new));
   var _all_;
run;

data _null_;
   retain rename "%sysfunc(repeat(%str( ), 80))";
   set names end=end;
   rename = catx(" ", rename, catx("=", old, new));
   if end then call symputx("rename", rename);
run;

%put rename=***&rename***;

[FriedEgg]

chang_y_chung@hotmail.com

I like your method of length initialization for the retain statement instead of using the length statement.  Very nice.

[Haikuo · Accepted solution]

Not exactly what you want, but can save some of your typing. However, it still requires one pass of datastep, so it would not boost efficiency much.

data have;

input (ABC F1 F2 FDG)($);

cards;

ID Name Sex Country

1 ABC M IND

2 BCD F USA

3 CDE M GER

4 DGE M UK

;

proc transpose data=have(obs=1) out=temp;

var _all_;

run;

proc sql ;

   select catx('=',_name_,col1)

     into :rename separated by ' '

        from temp;

quit;

data have (rename=(&rename));  set have;  if _n_>1; run;

proc print data=have;run;

Edit: Oooooooooops

[Linlin]

Hi Haikuo,

This definitely show that we are excellent students of Art, Tom, and Ksharp!  - Linlin

[Haikuo]

Can't agree more!

BTW, Ksharp is in between jobs, so he maynot have a reliable access to SAS for the time being. Maybe that is why we haven't seen him much for the past a few days.

[Ksharp]

Thank you HaiKuo and Linlin, to mention me .

I am very frustrated .

It is very hard to find a job about SAS Programmer in China. I don't know where to go.

SAS is not popular in China. Maybe I pick up a wrong-direction job, Now I have been studying Java for a few monthes.

Ksharp

[Linlin]

Hi Ksharp,

You are so good with SAS, I think SAS should offer you a job. Thank you very much for helping all of us!

[FriedEgg]

One step solution:

%let input=abc f1 f2 fdg;

%let vinput="%sysfunc(prxchange(s/\x20/%str(" ")/,-1,&input))";

data have(rename=(&rename));

input (&input) ($);

array _v[4] &input;

if _n_=1 then do;

  array _s[4] $ (&vinput);

  length _x $ 100;

  do _n=1 to dim(_s);

   _r=catx('=',_s[_n],_v[_n]);

   _x=catx(' ',_x,_r);

  end;

  call symputx('rename',_x);

end;

else output;

drop _:;

cards;

ID Name Sex Country

1 ABC M IND

2 BCD F USA

3 CDE M GER

4 DGE M UK

;

proc print noobs;

run;

ID	Name	Sex	Country
1	ABC	M	IND
2	BCD	F	USA
3	CDE	M	GER
4	DGE	M	UK

P.S.  KSharp/others if you happen to read this check out my profile status for a link to two jobs I am currently hiring for (in the USA though)

[Ksharp]

Matt,

I have send you an E-mail. Check it out.

Ksharp

[PGStats]

Or everything can be done in SQL :

data have;
input (ABC F1 F2 FDG)($);
cards;
ID Name Sex Country
1 ABC M IND
2 BCD F USA
3 CDE M GER
4 DGE M UK
;

proc sql noprint;

select name into :names separated by ','
from dictionary.columns
where libname="WORK" and memname="HAVE";

select catx(" ",&names.) into :vals
from have(obs=1);

select catx(" AS ", name, scan("&vals.", varnum)) into :cmd separated by ','
from dictionary.columns
where libname="WORK" and memname="HAVE";

reset nowarnrecurs;
create table have as
select &cmd. from have(firstobs=2);

quit;

PG

[Linlin]

Hi FE and PG,

Thank you for your reply!  it is very enjoyable for me to go through all the interesting solutions!  - Linlin