How to format a variable including year only (e.g. 2001) and year/month (Jun2001)?

[aw016]

Hi,

I'm working on a dataset containing participants' answers.

For one question about the date, some people answered only the year, like "2001", some people answered year and month, like "June 2001". The SAS showed the error " Variable Date has been defined as both character and numeric." when I tried to merge 3 similar datasets. 

I wonder how to define and format this date variable to keep all values, and still can merge successfully?

Thank you very much!

[PaigeMiller]

---

@aw016 wrote:

I'm working on a dataset containing participants' answers.

For one question about the date, some people answered only the year, like "2001", some people answered year and month, like "June 2001". The SAS showed the error " Variable Date has been defined as both character and numeric." when I tried to merge 3 similar datasets. 

---

Show us the log of this step that produced the error. We need to see the entire log for this step (in other words, with nothing chopped out) so we can see the code as it appears in the log, plus all ERRORs, WARNINGs and NOTEs. Please copy this part of the log as text and paste it into the window that appears when you click on the </> icon, this preserves the formatting and makes the log easier to read and decipher.

Also, SAS cannot have a single numeric variable appear as 2001 and other times appear as Jun2001. If you want to leave these variables as character then you can do this, but that causes other date functions to be not usable, such as ordering by year/month/day, and computing values from dates. I guess that's up to you.

[aw016]

2    data WS.combo;
3       set WS.n_bl WS.n_6 WS.n_12;
ERROR: Variable N1B has been defined as both character and numeric.
ERROR: Variable N1B has been defined as both character and numeric.
4       by study_ID;
5    run;

NOTE: The SAS System stopped processing this step because of errors.
WARNING: The data set WS.combo may be incomplete.  When this step was stopped there were 0 observations and 112 variables.
WARNING: Data set WS.combo was not replaced because this step was stopped.
NOTE: DATA statement used (Total process time):
      real time           0.10 seconds
      cpu time            0.03 seconds

Hi PaigeMiller,

Thank you for your reply. Please see the log record above, this error happened when I tried to combine 3 datasets with the same participants' ID. 

The original excel dataset includes both "2001" and "Jun-2001" since these were records directly from participants' answers in the questionnaire. The question only requires to answer month and year. I prefer to keep all answers as character variables.

[PaigeMiller]

You can't use the data step SET command when the variable is numeric in one data set and character in another data set. You have to modify this variable in one (or more) data sets so that now this variable in ALL data sets used, so that it is numeric in all data sets, or character in all data sets.

As I stated earlier, it is probably best that you make the variable character in all data sets, so you can have '2001' in some records and 'Jun-2001' in other records. You can't do this with numeric values. There are of course major disadvantages to doing this, as now sorting by date is a meaningless operation, can't be done, isn't defined.

As an example of changing N1B from numeric to character in one data set:

data n_bl;
     set ws.n_bl(rename=(n1b=n1b_numeric));
     n1b=put(n1b_numeric,4.);
run;

[Tom]

How did the variable get defined differently?  Did you use PROC IMPORT to guess how to define the variables?

Store the raw values as character and then come up with your own logic for how to "impute" a date if you need it.

[Sajid01]

Hello @aw016 
Consider the data variable as a character variable, That way the data can be imported.
Thereafter you can process as needed. I am giving a proof of concept sample code below. Modify as needed.

data test;
input @1 date_ & $20. ;
datalines;
2001
June 2001
15/06/2001
06/15/2001
;
run;

[aw016]

Hi Tom,

Thank you for your reply.

I use "%let...$path..." to import .csv files. The different values were directly from questionnaire records. Some people answered the only year, some people answered month and year. The question requires for month and year by the way, and it showed as MM/YYYY on the questionnaire, "JUN-01" in excel, and "JUN2001" in the SAS file; if there is a missing value, it shows as a dot; if the answer only contains the year, it shows as "2001". Below is my code for import.

**import a csv file;
%let path=C:\Project;
options validvarname= v7;

proc import datafile="&path\Project\WS.csv"
                    out=N_bl
	            dbms=csv replace;
	            guessingrows=max;
run;

[Tom]

If you want consistent dataset structure do NOT use PROC IMPORT to read text files.

Write your own data step to read the files so that the variable names and types are consistent.

Basic datastep is simple.  Just fill in the details for you actual variables.

data N_bl;
  infile "&path\Project\WS.csv" dsd truncover firstobs=2;
  input var1 var2 :$20. ..... ;
run;