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.
Use *
with a drop= dataset option in the from clause like
FROM Step5.ar_number_of_patent_end(drop=var1 var2 var3....) AS end
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
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
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.
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)
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.