topic Re: create a new data which include multiple variables by SQL in SAS Programming

create a new data which include multiple variables by SQL

France — Tue, 11 Sep 2018 19:34:57 GMT

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.

Re: create a new data which include multiple variables by SQL

novinosrin — Tue, 11 Sep 2018 19:36:55 GMT

Use *

with a drop= dataset option in the from clause like

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

Re: create a new data which include multiple variables by SQL

Reeza — Tue, 11 Sep 2018 19:39:50 GMT

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.

Re: create a new data which include multiple variables by SQL

Reeza — Tue, 11 Sep 2018 19:42:59 GMT

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.

Re: create a new data which include multiple variables by SQL

PGStats — Tue, 11 Sep 2018 20:05:11 GMT

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)

Re: create a new data which include multiple variables by SQL

France — Tue, 11 Sep 2018 20:06:59 GMT

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

Re: create a new data which include multiple variables by SQL

France — Tue, 11 Sep 2018 20:15:43 GMT

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

Re: create a new data which include multiple variables by SQL

Reeza — Tue, 11 Sep 2018 21:02:57 GMT

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