DATA Step, Macro, Functions and more

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

Accepted Solution Solved
Reply
Super Contributor
Posts: 1,636
Accepted Solution

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

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


Accepted Solutions
Solution
‎04-04-2012 09:59 AM
Respected Advisor
Posts: 3,124

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

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

View solution in original post


All Replies
Super User
Posts: 5,083

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

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;

Super Contributor
Posts: 1,636

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

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!

Super User
Posts: 5,083

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

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.

Regular Contributor
Posts: 241

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

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***;

Trusted Advisor
Posts: 1,300

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

chang_y_chung@hotmail.com

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

Solution
‎04-04-2012 09:59 AM
Respected Advisor
Posts: 3,124

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

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

Super Contributor
Posts: 1,636

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

Hi Haikuo,

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

Respected Advisor
Posts: 3,124

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

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.

Super User
Posts: 9,681

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

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

Super Contributor
Posts: 1,636

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

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!

Trusted Advisor
Posts: 1,300

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

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;

IDNameSexCountry
1ABCMIND
2BCDFUSA
3CDEMGER
4DGEMUK

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)

Super User
Posts: 9,681

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

Matt,

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

Ksharp

Respected Advisor
Posts: 4,649

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

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

PG
Super Contributor
Posts: 1,636

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

Hi FE and PG,

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

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 18 replies
  • 1229 views
  • 5 likes
  • 8 in conversation