cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
Jarvin99
‎12-17-2023
  • 33 Posts
  • 7 Likes Given
  • 0 Solutions
  • 0 Likes Received

fill in missing values with the previous values for panel

by Obsidian | Level 7 in SAS Programming
‎12-15-2023 10:56 AM
‎12-15-2023 10:56 AM
data have; input gvkey $ year shrcd; datalines; 2477 2001 11 2477 2002 11 2477 2003 3156 2001 10 3156 2002 10 3156 2003 3156 2004 3010 2001 3010 2002 3010 2003 3010 2004 ; proc print data=have; run; Hi everyone, I am new to SAS, so the data step to input the data that I am writing here may contain some flaws, but I did my best to help you visualize my panel.  I only want to fill in the column "shrcd" (as there actually many columns in my dataset with missing values) if this gvkey once had its shrcd filled in.  Hence, gvkey 2477 should have its 2003's shrcd as 11, and gvkey 3156 should have both its 2003 and 2004 filled in as 10, but gvkey 3010 should NOT have anything filled in for its shrcd.   Could I kindly seek assistance on how to do my request? Thank you. ... View more

Re: match the exact same word

by Obsidian | Level 7 in SAS Programming
‎12-13-2023 01:29 AM
‎12-13-2023 01:29 AM
Hi, Thank you so much for your code. I managed to solve my problem using the following codes: data education; set org.na_dir_profile_education; if findw(upcase(qualification), 'BACHELOR')>=1 then bachelor = 1; if findw(upcase(qualification), "BACHELOR'S DEGREE")>=1 then bachelor = 1; if findw(upcase(qualification), 'BS')>=1 then bachelor = 1; if findw(upcase(qualification), 'BSC')>=1 then bachelor = 1; if findw(upcase(qualification), 'BE')>=1 then bachelor = 1; if findw(upcase(qualification), 'BSE')>=1 then bachelor = 1; if findw(upcase(qualification), 'BENG')>=1 then bachelor = 1; if findw(upcase(qualification), 'BA')>=1 then bachelor = 1; if findw(upcase(qualification), 'B Acc')>=1 then bachelor = 1; if findw(upcase(qualification), 'B Arch')>=1 then bachelor = 1; if findw(upcase(qualification), 'B.Acc')>=1 then bachelor = 1; if findw(upcase(qualification), 'B.Math')>=1 then bachelor = 1; if findw(upcase(qualification), 'B.Proc')>=1 then bachelor = 1; if findw(upcase(qualification), 'BAS')>=1 then bachelor = 1; if findw(upcase(qualification), 'BASc')>=1 then bachelor = 1; if findw(upcase(qualification), 'BAppSc')>=1 then bachelor = 1; if findw(upcase(qualification), 'BArch')>=1 then bachelor = 1; if findw(upcase(qualification), 'BBA')>=1 then bachelor = 1; if findw(upcase(qualification), 'BBM')>=1 then bachelor = 1; if findw(upcase(qualification), 'BBS')>=1 then bachelor = 1; if findw(upcase(qualification), 'BCA')>=1 then bachelor = 1; if findw(upcase(qualification), 'BCL')>=1 then bachelor = 1; if findw(upcase(qualification), 'BCom')>=1 then bachelor = 1; if findw(upcase(qualification), 'BComm')>=1 then bachelor = 1; if findw(upcase(qualification), 'BCompt')>=1 then bachelor = 1; if findw(upcase(qualification), 'BEc')>=1 then bachelor = 1; if findw(upcase(qualification), 'BEcon')>=1 then bachelor = 1; if findw(upcase(qualification), 'BEd')>=1 then bachelor = 1; if findw(upcase(qualification), 'BFA')>=1 then bachelor = 1; if findw(upcase(qualification), 'BInf')>=1 then bachelor = 1; if findw(upcase(qualification), 'BInfTech')>=1 then bachelor = 1; if findw(upcase(qualification), 'BJ')>=1 then bachelor = 1; run; May I know if there is simplified way like writing a macro to shorten the above, as there are at least 30-40 qualified words for my bachelor indicator?   Thank you. ... View more

match the exact same word

by Obsidian | Level 7 in SAS Programming
‎12-12-2023 10:38 PM
‎12-12-2023 10:38 PM
Hi, I would say I am very new to SAS...so please kindly help me. I am struggling with a very simple task. I want to be able to identify people with a bachelor's degree. Below is the sample of the data: people obtain_year qualification Tom 1990 BA Tom 2002 master Tom 2010 MBA Alice 1994Jan bachelor's degree Alice 2011 EMBA Lou 2005 BS Lou 2006Jun01 MSc Lou 2012 master of business administration   My desired outcome should create columns of indicators flagging who has bachelor, master, and MBA degrees, regardless of the uppercase and lowercase. people obtain_year qualification bachelor master MBA Tom 1990 BA 1     Tom 2002 master   1   Tom 2010 MBA     1 Alice 1994Jan bachelor's degree 1     Alice 2011 EMBA       Lou 2005 BS 1     Lou 2006Jun01 MSc   1   Lou 2012 master of business administration     1   I tried using 'findw' and 'index', but these functions will pick 'MBA' as well when I only want to include 'BA'. Bachelor degrees should be equal to 1 when the qualification contains the exact words: 'bachelor', 'BA', 'BS', or 'bachelor's degree'.   Thank you so much for your assistance in advance, as I really need to get this done soon in order for the next step of my research. ... View more

Re: keep multiple rows based the conditions

by Obsidian | Level 7 in SAS Programming
‎09-23-2023 01:16 PM
‎09-23-2023 01:16 PM
Emmm...I need to do it because my startyear, startmonth, and startday can be missing. So in cases where my startmonth and startday are missing, I can only compare startyear with the year of the fiscal date. Same thing for the cases where startmonth and startyear are non-missing but the startday is missing. FYI, startyear, startmonth and startday are from an employee's start job date whose form can be yyyy-mm, yyyy, or yyyy-mm-dd, depending on the information available. ... View more

Re: keep multiple rows based the conditions

by Obsidian | Level 7 in SAS Programming
‎09-23-2023 12:18 PM
‎09-23-2023 12:18 PM
Thank you both! I went back to rectify my mistake of the fiscal year, fiscal begin date, and fiscal end date. Below are my new codes: data ciqt.company_matching9; set ciqt.company_matching9; fyrbegyear = year(fyrbegdt); fyrbegmonth = month(fyrbegdt); fyrbegday = day(fyrbegdt); fyrendyear = year(fyrenddt); fyrendmonth = month(fyrenddt); fyrendday = day(fyrenddt); run; data ciqt.company_matching10; set ciqt.company_matching9; if not missing(startyear) and not missing(startmonh) and not missing(startday) then do; if fyrbegyear>=startyear and fyrbegmonth>=startmonth and fyrbegday>=startday then output; end; else if not missing(startyear) and not missing(startmonh) and missing(startday) then do; if fyrbegyear>=startyear and fyrbegmonth>=startmonth then output; end; else if not missing(startyear) and missing(startmonh) then do; if fyrbegyear>=startyear then output; end; else if missing(startyear) then output; run; I think my logic is correct, but I do not know why, for example: an observation of fyrbegdt=01JAN2010, startyear=2010, startmonth=10, and missing startday is still kept in my dataset in the end.   Please let me know if I should provide a subset of my raw data. Thank you. ... View more

Re: keep multiple rows based the conditions

by Obsidian | Level 7 in SAS Programming
‎09-17-2023 01:44 PM
‎09-17-2023 01:44 PM
Do you mean any part of the year is between the two dates? Answer: yes. So any date of the fiscal_year 2000 should be between gvkey_startdate and gvkey_enddate.     How does the logic work when gvkey_enddate is missing? Explain that too. Answer: if the gvkey_enddate is missing, treat it as not ending yet. The reason why there is this gvkey_startdate and gvkey_enddate is due to the M&A. Hence, a company_id named 'Abcd' was acquired by the gvkey=8801 on 20Jan2011 and continues to be acquired until now.   Hope my reply clarifies! Thank you for your help. ... View more

keep multiple rows based the conditions

by Obsidian | Level 7 in SAS Programming
‎09-17-2023 09:56 AM
‎09-17-2023 09:56 AM
Hi all, I am really new to SAS...I would like to know whether I can remove certain rows by restricting that gvkey_startdate <= fiscal_year <= gvkey_enddate. The raw database looks like this: Person_id Company_id gvkey Gvkey_startdate Gvkey_enddate Fiscal year roa 1 Abcd 1234 27Nov1999 27Sep2010 2000 7.35 1 Abcd 8801 20Jan2011   2000 7.35 1 Abcd 1234 27Nov1999 27Sep2010 2012 6.42 1 Abcd 8801 20Jan2011   2012 6.42 2 Sdfg 2367 01Apr2000   2001 1.1 2 Sdfg 2367 01Apr2000   2002 1.49 2 Cvbn 5678 01Sep1999 30Jun2005 2000 0.52 3 Cvbn 5678 01Sep1999 30Jun2005 2001 0.53 3 Cvbn 5678 01Sep1999 30Jun2005 2002 0.91   The final product I want should be like this: Person_id Company_id gvkey Gvkey_startdate Gvkey_enddate Fiscal_year roa 1 Abcd 1234 27Nov1999 27Sep2010 2000 7.35 1 abcd 8801 20Jan2011   2012 6.42 2 Sdfg 2367 01Apr2000   2001 1.1 2 Sdfg 2367 01Apr2000   2002 1.49 2 Cvbn 5678 01Sep1999 30Jun2005 2000 0.52 3 Cvbn 5678 01Sep1999 30Jun2005 2001 0.53 3 cvbn 5678 01Sep1999 30Jun2005 2002 0.91   It will definitely help me a lot if you have a solution if you can remove a row based on other columns. Thank you. ... View more

Re: fuzzy matching

by Obsidian | Level 7 in SAS Programming
‎10-20-2022 01:09 PM
‎10-20-2022 01:09 PM
What if I do not restrict? Why my 'country' variable is empty after merge?  Also, do you mean it is 1-to-1, so there is no optimization in this case? Then, how should I do to obtain 'gvkey' from Com2021 for firms in Sp using firm names?   Thank you. ... View more

fuzzy matching

by Obsidian | Level 7 in SAS Programming
‎10-20-2022 11:11 AM
‎10-20-2022 11:11 AM
Hi all, I face an error during fuzzy merging, and attached are my two sample datasets. Below is my code: proc sql; create table try as select a.gvkey, a.comnam, b.*, compged(a.comnam, b.name) as compged_score from com2021 as a, sp as b where calculated compged_score le 50 order by b.name; quit; The merged data 'try' is supposed to contain all variables filled, so I am not sure why some variables are empty. The log shows " The execution of this query involves performing one or more Cartesian product joins that can not be optimized."   I am not sure how to solve the log. Pls helps! ... View more

Re: a list of values to filter

by Obsidian | Level 7 in SAS Programming
‎08-19-2022 01:16 PM
‎08-19-2022 01:16 PM
Sorry that I am a rookie for SAS. May I know why my example is syntactically incorrect? Why cannot I use 'if' but 'in'? I always cannot distinguish the difference, and the program seems to run okay for 'if' selection. Thank you, and you provide a great solution. ... View more

a list of values to filter

by Obsidian | Level 7 in SAS Programming
‎08-19-2022 12:27 PM
‎08-19-2022 12:27 PM
I have a list of values to filter for a variable called codeID. Normally, what I will do is to use proc sql and say if codeID= 'a111' or 'a112' or 'a113'. However unlike the example with just 3 values, with 55 values to select, I am not sure if there is a faster way to do. The values are not ordinal, meaning that they can be 'S009' or 'a111' or 'S712'.   Thank you for your help. ... View more

How to merge two datasets with the same column names into one dataset

by Obsidian | Level 7 in SAS Programming
‎07-10-2022 11:47 PM
‎07-10-2022 11:47 PM
Hello, below is an example of my two datasets: dataset A: id env water transport production 1 0 0 0 0 2 1 0 1 0 3 0 0 0 1 4 0 0 1 0 dataset B: id env water transport production 1 1 0 0 0 2 0 0 0 0 3 0 0 0 0 4 1 0 1 1   Just a background, why two datasets have the same column names is because I split my original data into two subsamples and let them run through the same codes to achieve this.   I want that as long as there is '1' in one of the datasets, the combined table should reflect '1'. Below is my desired outcome: id env water transport production 1 1 0 0 0 2 1 0 1 0 3 0 0 0 1 4 1 0 1 1 I know there are probably time-consuming ways to achieve the desired outcome by renaming one of the table's column names, merging by id, summing the columns, restricting to greater than 1, and so on. However, I want to know if there is a fast and non-tedious way for operation as the reason why I initially split is that the original dataset was too huge. Hopefully, the expert can provide a more efficient method. Thank you.  ... View more

extract any words after the fourth location

by Obsidian | Level 7 in SAS Programming
‎07-08-2022 10:35 AM
‎07-08-2022 10:35 AM
Hi all, an example of what I want is below: original string what I want G01S7 7 B32B3 3 B32B2605 2605 F05B2240 2240   I want to extract any numbers/ substrings after the fourth position of the original string. May I know if there is a way to do so? Thank you. ... View more

how to import tsv file

by Obsidian | Level 7 in SAS Programming
‎06-27-2022 12:11 PM
‎06-27-2022 12:11 PM
Dear all, I really encounter this error: ERROR: Invalid file, D:\Dropbox\Dataset\PatentsView\patent.tsv.   My code is: data pav.patent ; %let _EFIERR_ = 0; /*set the ERROR detection macro variable*/ infile 'D:\Dropbox\Dataset\PatentsView\patent.tsv' delimiter='09'x MISSOVER DSD lrecl=32767 firstobs=2; informat id 8.; informat type $100.; informat number 8.; informat country $20.; informat date $12.; informat abstract $1100.; informat title $200.; informat kind $10.; informat num_claims 8.; informat filename $200.; informat withdrawn 8.; format id 8.; format type $100.; format number 8.; format country $20.; format date $12.; format abstract $1100.; format title $200.; format kind $10.; format num_claims 8.; format filename $200.; format withdrawn 8.; ; input id -- withdrawn ; run; I do not know where goes wrong. I download this tsv file from the "patent" file via this link: https://patentsview.org/download/data-download-tables. Thank you. ... View more

Re: Import csv with variables of infinite decimals

by Obsidian | Level 7 in SAS Programming
‎06-25-2022 06:36 AM
‎06-25-2022 06:36 AM
Thank you for your reply. So you mean even if I choose length 8 for my variables xi_real and xi_nominal, the number of decimal digits will still be up to 15?   So sorry for wrong type of the file. Here you go. ... View more
Contact Me
Online Status
Offline
Date Last Visited
‎12-17-2023 05:28 AM
Likes given to
View All
Quick links