create a new data which include multiple variables by SQL

[France]

Dear all

I would like to create a new data which include multiple variables,  I cannot use * because there are some variables I do not want to input. 

PROC SQL;
CREATE TABLE Step5.number_of_patent_us AS
SELECT 
  end.psn_name,
  application200001,
  application200002,
  application200003,
  ....
  application201012

FROM Step5.ar_number_of_patent_end AS end
JOIN Pat_ori.Companies AS co ON end.psn_name = co.psn_name
WHERE person_ctry_code='US'
ORDER BY end.psn_name
;
QUIT;

could you please give me some suggestion? or what should I do  if PROC SQL cannot work

thanks in advance.

[novinosrin]

Use *

with a drop= dataset option in the from clause like

FROM Step5.ar_number_of_patent_end(drop=var1 var2 var3....) AS end

[France]

dear novinosrin,

thanks for your reply.

could you please give me some suggestions if I want to delete all variables in pat_ori.Companies, and the reason I join this data set is I want use the person_ctry_code in it to identify the US company.

however, it seems that I cannot get the result after dropping all variables in pat_ori.Companies

[Reeza]

If you only want data from a single table, use an alias with the *

select t1.* /*selects all data from t1 table*/
         , t2.country_code
from [table name] as t1
join [table name] as t2

[Reeza]

A data step supports variable lists, whereas SQL does not. 

---

@France wrote:

Dear all

I would like to create a new data which include multiple variables,  I cannot use * because there are some variables I do not want to input. 

PROC SQL;
CREATE TABLE Step5.number_of_patent_us AS
SELECT 
  end.psn_name,
  application200001,
  application200002,
  application200003,
  ....
  application201012

FROM Step5.ar_number_of_patent_end AS end
JOIN Pat_ori.Companies AS co ON end.psn_name = co.psn_name
WHERE person_ctry_code='US'
ORDER BY end.psn_name
;
QUIT;

could you please give me some suggestion? or what should I do  if PROC SQL cannot work

thanks in advance.

---

[Reeza]

On second thought, perhaps you should restructure your data first. Make it long rather than wide and it's much easier to work with. If you need some suggestions regarding data structures, I recommend the "Tidy Data" paper by Hadley Wickham. Although the code is in R, it's still the same principles.

[France]

Dear Reeza, many thanks for your recommendation. I will read the paper in the future.

[PGStats]

You may select US patents and transpose your data with:

proc sql;
create view ar_number_of_patent_us as
SELECT *
FROM    
    Step5.ar_number_of_patent_end AS end inner join
    Pat_ori.Companies AS co ON end.psn_name = co.psn_name
WHERE co.person_ctry_code='US';
QUIT;

data Step5.number_of_patent_us;
set ar_number_of_patent_us;
array a application200001 -- application201012;
do i = 1 to dim(a);
    y = substr(vname(a{i}), 12, 4);
    m = substr(vname(a{i}), 16, 2);
    month = mdy(input(m, best.), 1, input(y, best.));
    nbPatent = a{i};
    output;
    end;
format month yymmp.;
keep psn_name month nbPatent;
run;

(untested)